AND Before OR: Operator Precedence in WHERE Clauses

Operator precedence decides how SQL Server groups AND and OR in a WHERE clause, and it does not read your filter the way you do. AND binds tighter than OR. So a filter that sounds right in English can quietly let the wrong rows through.

The report that shows closed orders

Here is a call every DBA gets. A manager says, “The open orders report has closed orders in it.” You open the query and it looks fine. Status is Open, region is East or West. What could be wrong?

Let me rebuild it with four orders. Run both queries. The first is the report as written. The second adds one pair of parentheses.

DROP TABLE IF EXISTS #Orders;
CREATE TABLE #Orders (OrderId int, StatusName varchar(10), Region varchar(10));
INSERT #Orders VALUES (1,'Open','East'), (2,'Closed','West'),
                      (3,'Open','West'), (4,'Closed','East');

SELECT * FROM #Orders
WHERE StatusName = 'Open' AND Region = 'East' OR Region = 'West'
ORDER BY OrderId;

SELECT * FROM #Orders
WHERE StatusName = 'Open' AND (Region = 'East' OR Region = 'West')
ORDER BY OrderId;

The first query returns orders 1, 2 and 3. Order 2 is Closed, and it is in the report. The second query returns only orders 1 and 3. That is what the manager wanted.

How SQL Server reads AND and OR

The engine does not read left to right. It evaluates comparisons first, then NOT, then AND, and OR last. So the first query really means: (Open AND East) OR West.

Read it that way and the bug is obvious. Every West row passes the second half, no matter its status. Order 2 is the stray row.

You do not have to eyeball it. EXCEPT subtracts one result from the other and names the rows that differ. A count alone can fool you, because two wrong filters can return the same number of rows.

SELECT OrderId FROM #Orders
WHERE StatusName = 'Open' AND Region = 'East' OR Region = 'West'
EXCEPT
SELECT OrderId FROM #Orders
WHERE StatusName = 'Open' AND (Region = 'East' OR Region = 'West');

One row comes back, order 2. That is the closed order the manager found.

NOT only grabs what comes next

NOT binds even tighter than AND. Without parentheses, it negates only the comparison right after it. That surprises people who want to exclude a whole group.

The block below runs three versions over four regions, one of them NULL. The first two return North only. They are the same rule written two ways. The third forgets the parentheses and brings West back.

SELECT v.Region FROM (VALUES ('East'), ('West'), ('North'), (NULL)) AS v(Region)
WHERE NOT (v.Region = 'East' OR v.Region = 'West')
ORDER BY v.Region;

SELECT v.Region FROM (VALUES ('East'), ('West'), ('North'), (NULL)) AS v(Region)
WHERE v.Region <> 'East' AND v.Region <> 'West'
ORDER BY v.Region;

SELECT v.Region FROM (VALUES ('East'), ('West'), ('North'), (NULL)) AS v(Region)
WHERE NOT v.Region = 'East' OR v.Region = 'West'
ORDER BY v.Region;

The third query reads as (NOT East) OR West. North passes the first half and West passes the second. So both come back.

Notice the NULL row never shows up in any of the three. A comparison with NULL is neither true nor false, it is unknown, and WHERE keeps only true rows. If your rule treats a missing region as its own case, test for NULL on purpose.

Arithmetic follows the same idea

Multiplication goes before addition, just as AND goes before OR. Parentheses change the order in both. Division has its own trap: two integers divide into an integer. Here 5 / 2 gives 2, while a decimal gives 2.5.

SELECT 10 + 5 * 2 AS multiplication_first,
       (10 + 5) * 2 AS grouped_addition,
       5 / 2 AS integer_division,
       CAST(5 AS decimal(10,2)) / 2 AS decimal_division;

DROP TABLE IF EXISTS #Orders;

Make the grouping visible

Use parentheses whenever AND and OR share a WHERE clause, even if the default order already works. The rule never changes. The query does.

Next month someone adds one more condition at the end. Without parentheses, that small edit can change which rows qualify. With them, the next reader sees your intent at once.

When a report shows a row it should not, read the WHERE clause the way the engine does.

A mixed filter is not a sentence, it is a rule you group on purpose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.