SQL WHERE with AND and OR: use parentheses to exclude canceled orders

You meant to count paid orders, but one canceled order appears in the result. The row is valid; the problem is in WHERE. The way a person grouped the mixed AND and OR conditions differs from how SQL evaluates them.

Which rows survive an ungrouped AND/OR condition?

Suppose you want paid orders from Seoul or Busan. Keep the example small enough to trace every row.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO orders (id, status, city) VALUES
  (1, 'paid', 'Seoul'),
  (2, 'paid', 'Busan'),
  (3, 'canceled', 'Busan'),
  (4, 'canceled', 'Seoul'),
  (5, 'pending', 'Incheon'),
  (6, 'paid', 'Incheon');

At a glance, this query can look like “paid and (Seoul or Busan)”:

SELECT id, status, city
FROM orders
WHERE status = 'paid' AND city = 'Seoul' OR city = 'Busan'
ORDER BY id;

id  status    city
1   paid      Seoul
2   paid      Busan
3   canceled  Busan

Row 3 is canceled. AND has higher precedence than OR, so the condition is evaluated like (status = 'paid' AND city = 'Seoul') OR city = 'Busan'. Every Busan row passes the right-hand branch regardless of status.

The diagram highlights the row that should be excluded: canceled Busan order 3 passes only the ungrouped condition. Grouping the city alternatives leaves paid orders 1 and 2.

Put parentheses around the city alternatives

The paid condition must apply to both cities:

SELECT id, status, city
FROM orders
WHERE status = 'paid'
  AND (city = 'Seoul' OR city = 'Busan')
ORDER BY id;

id  status  city
1   paid    Seoul
2   paid    Busan

Row 3 now fails the paid condition even though it is in Busan. Parentheses make the intended logical group explicit; they are not merely formatting.

Can IN express the same city condition?

For alternatives in one column, IN is shorter. Here it returns the same two rows as the parenthesized query:

SELECT id, status, city
FROM orders
WHERE status = 'paid'
  AND city IN ('Seoul', 'Busan')
ORDER BY id;

id  status  city
1   paid    Seoul
2   paid    Busan

IN fits a set of candidate values for the same column. It cannot replace an arbitrary group of conditions on different columns, such as status and date. Keep explicit parentheses for those groups.

Test inclusion and exclusion, not just the row count

A result count of two could still contain the wrong two rows. Check the IDs that must be included and excluded. In this example, orders 1 and 2 must remain; canceled Busan order 3 and paid Incheon order 6 must not.

When WHERE sets a report or access boundary, test a row that should fail each new OR branch. A query that executes successfully has not necessarily selected the correct rows.

Key takeaways

If a mixed AND/OR filter admits unexpected rows, write out its parenthesized meaning. Here the ungrouped condition admits a canceled Busan order. status = 'paid' AND (city = 'Seoul' OR city = 'Busan') returns only the intended two. IN can shorten the alternatives for one column, and testing should verify actual included and excluded IDs as well as counts.