SQL AND OR precedence - why your WHERE returns wrong rows

-- July's failed payments from the two pilot regions, biggest first.

SELECT id, region, status, amount

FROM payments

WHERE strftime('%Y-%m', created_at) = '2026-07'

  AND status = 'failed'

  AND region = 'EU'

  OR region = 'UK'

ORDER BY amount DESC;

Row two should not exist. The report promises failed payments from July, and there it sits: a 700 succeeded payment, wearing a green checkmark in the very list the refunds team works through. Further down, a payment from June. The query has a date filter and a status filter, and both are spelled correctly.

Here is the report query, meant to list July's failed payments from two pilot regions:

-- July's failed payments from the two pilot regions, biggest first.
SELECT id, region, status, amount
FROM payments
WHERE strftime('%Y-%m', created_at) = '2026-07'
  AND status = 'failed'
  AND region = 'EU'
  OR region = 'UK'
ORDER BY amount DESC;

Why does my SQL WHERE with AND and OR return extra rows?

Line 7 is the bug. AND has higher precedence than OR in every SQL engine, so the WHERE parses as (july AND failed AND region = 'EU') OR (region = 'UK'). The bare OR opens a second filter that only checks the region, so every UK row qualifies, whatever its status or month. Parenthesize the region pair: AND (region = 'EU' OR region = 'UK').

A bare OR splits your WHERE into two filters

The MySQL operator precedence table puts AND a full level above OR, and Postgres, SQL Server, Oracle, and SQLite agree, because the standard does. Precedence means the parser glues every AND chain together first and hands OR whatever is left on each side. Read the query the way the parser does and the WHERE clause is two rival filters welded at the OR: a strict one that demands July, failed, and EU, and a second one, four characters long, that demands UK and nothing else.

Indentation is what makes it hard to see. All four conditions line up as one vertical checklist, so the eye reads them as one conjunction with a small alternative tucked into the region part. The parser does not see the indentation. Meanwhile the loudest line in the query, the strftime on line 4 with its format string, is the one a reviewer slows down on, and it is doing its job correctly in both branches of the accidental split.

Run against a six-row payments table, the query returns this (real output, SQLite):

id	region	status	amount	why it is here
1	EU	failed	900	matches the intended filter
4	UK	succeeded	700	UK, so the second filter waves it in
3	UK	failed	250	correct, by coincidence
5	UK	failed	120	June - the date filter never saw it

The bug is data-dependent, which is why it survives review. On a staging database where the UK rows happen to be July failures anyway, buggy and correct queries return identical results. The report ships, runs happily for a sprint, and breaks the day the first UK payment succeeds.

The refund sweep that revisited a settled charge

Follow that row 4 forward. A refunds job reads the "failed payments" report and queues each row for reversal. The 700 succeeded charge gets queued with the rest, refunded, and closed. Finance sees the books off by exactly 700, support sees a customer delighted about a free purchase, and the on-call engineer diffs the refund queue against the payments table and finds a succeeded charge that a failed-only query somehow produced. The query gets read five times by three people before someone reads it aloud without the line breaks, and the two filters come apart in their mouth mid-sentence.

Query builders are not immune: SQLAlchemy shipped a bug where AND nested inside OR was emitted without parentheses, producing this exact wrong-rows class from correct-looking ORM code.

The fix: parentheses, or IN when the OR is really a list

Group the alternative explicitly:

WHERE strftime('%Y-%m', created_at) = '2026-07'
  AND status = 'failed'
  AND (region = 'EU' OR region = 'UK')

On the same six rows this returns only the two July EU/UK failures. When the OR is enumerating values of one column, IN says it without touching precedence at all: AND region IN ('EU', 'UK'). Harder to misparse, easier to extend to a third region, and it reads as what it is, a list.

The tempting non-fix is rearranging the lines, moving the OR condition up or the date filter down, on the instinct that the last line is the one that dangles. Order changes nothing; precedence is not position. WHERE region = 'UK' OR region = 'EU' AND status = 'failed' AND ... is the same wrong query with the unguarded branch now first. Watch NOT too when you start grouping: it binds tighter than AND, so NOT region = 'EU' OR region = 'UK' negates only the first comparison.

Sweep your queries for a bare OR between ANDs

Any WHERE that contains both operators and no parentheses deserves a second read:

grep -rniE "where[^;]*\band\b[^;]*\bor\b|where[^;]*\bor\b[^;]*\band\b" --include=*.sql .

Check ORM call sites too, where or_() and | build the same trees, and raw SQL strings inside application code, which the --include above will miss. Linters help at the edges: sqlfluff and most SQL review tooling will format the query, but formatting is exactly what made this bug look safe, so treat a mixed AND/OR WHERE as un-mergeable until the parentheses are physically there. The habit that sticks: the moment a WHERE gains its first OR, wrap something.

Related bugs

Filters that lie are a running theme in SQL: a WHERE on a LEFT JOIN quietly turns it into an inner join, and status <> 'shipped' skips the NULL rows entirely - both, like this one, return a plausible result set with the wrong membership. More live on the SQL bug hub and in the full bug write-ups.

Before you close the tab: open your biggest report query and look for an OR. If you find one without parentheses around it, read the WHERE aloud the way the parser does, ANDs first. You will hear it split.