Operator precedence

Concepts covered: Operator Precedence

A WHERE clause with 3 conditions and both connectors has 2 possible readings, and the engine picks one by a fixed rule rather than by the order you typed them. A product team asks for active US users plus anyone who has spent more than 1000 regardless of location. The first attempt writes the conditions in that order.

SELECT
  user_id,
  country,
  status,
  total_spent
FROM users
WHERE country = 'US'
  AND status = 'active'
  OR total_spent > 1000

Four rows pass. Users 42 and 89 are active US users, and users 156 and 203 pass on spending alone although they live in Canada and the UK and are inactive. The engine read the clause as (country = 'US' AND status = 'active') OR total_spent > 1000. That happens to be what the product team asked for. An analyst who meant US users who are either active or big spenders gets the same 4 rows and no hint that 2 of them are wrong.

The binding order

Comparisons evaluate first: =, !=, <, >, IN, LIKE and BETWEEN each turn into TRUE, FALSE or NULL for the row. NOT applies next, to the single result right after it. AND joins its neighbours after that, and OR joins last. Because AND binds before OR, a chain mixing both splits at every OR, and the AND groups between the ORs are the pieces being chosen among.

Parentheses override the rule: whatever sits inside them resolves before anything outside. The 3 tabs below run the same conditions with no parentheses, with parentheses around the OR, and with parentheses that spell out the default reading.

Implicit

No parentheses, so AND groups first and OR joins the group to the spending test.

SELECT
  user_id,
  country,
  status,
  total_spent
FROM users
WHERE country = 'US'
  AND status = 'active'
  OR total_spent > 1000

Grouped

Parentheses around the OR, so every kept row must be from the US.

SELECT
  user_id,
  country,
  status,
  total_spent
FROM users
WHERE country = 'US'
  AND (status = 'active' OR total_spent > 1000)

Explicit

Parentheses that match the default reading and return the same rows as the first tab.

SELECT
  user_id,
  country,
  status,
  total_spent
FROM users
WHERE (country = 'US' AND status = 'active')
  OR total_spent > 1000

The grouped version returns 2 rows, users 42 and 89, since now every kept row must be from the US. The third tab returns the same 4 rows as the first, and that is its value: parentheses that match the default reading change nothing in the result and spare the next reader from recalling the rule. A reviewer scanning the first tab has to know the rule to know what it returns; a reviewer scanning the third does not.

NOT binds tight

NOT status = 'active' AND country = 'US' negates only status = 'active', so it keeps US users whose status is anything else. To negate the whole conjunction, wrap it: NOT (status = 'active' AND country = 'US') keeps every row that fails at least one of the 2 tests, including users 156, 203 and 310 from the table above. A NULL status always drops out of the first form, because NOT NULL is NULL. The wrapped form decides by country: for a non-US row, NULL AND FALSE is FALSE and NOT FALSE is TRUE, so the row survives; for a US row, NULL AND TRUE is NULL, NOT NULL is NULL, and the row drops.

Against the 5-row users table above, how many rows does this query return?

SELECT
  user_id
FROM users
WHERE status = 'inactive'
  OR country = 'US' AND total_spent > 600

• 1 row: only user_89 is both from the US and over 600
• 3 rows: the 3 inactive users, since the AND fails for all of them
• 5 rows: every user, because OR with any TRUE keeps the row
• 4 rows: the 3 inactive users plus user_89

Do

• Add parentheses whenever one WHERE mixes AND with OR
• Put each condition on its own line, indented under its connector
• Read a clause back as AND groups joined by OR before trusting it
• Test a mixed clause on a small table where you can count the expected rows

Avoid

• Rely on top to bottom reading to decide how conditions group
• Negate a group with a bare NOT in front of its first condition only
• Trust a query that returns the right count once; check which rows it returned

About this lesson

This section is part of the Filtering: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.