Level 2 · Gift ideas under $50

Real filters have several conditions. AND requires all of them, OR requires at least one:

SELECT name, unit_price
FROM products
WHERE category = 'Kitchen'
  AND unit_price >= 100;

The precedence trap

AND is evaluated before OR, the way multiplication comes before addition. This query looks right and is wrong:

-- bug: returns every Lighting product, plus cheap Decor products
WHERE category = 'Lighting' OR category = 'Decor' AND unit_price < 50

The database reads it as category = 'Lighting' OR (category = 'Decor' AND unit_price < 50). The price limit only applies to Decor. The fix is parentheses:

WHERE (category = 'Lighting' OR category = 'Decor') AND unit_price < 50

Rule of thumb: whenever AND and OR appear in the same WHERE clause, add parentheses, even if you think they are not needed. Your reviewer will thank you.

IN is a cleaner OR

IN checks a value against a list and makes the precedence issue disappear:

WHERE category IN ('Lighting', 'Decor')
  AND unit_price < 50

NOT IN excludes a list: WHERE status NOT IN ('cancelled', 'refunded'). Careful though: if the list can contain a NULL (for example when it comes from a subquery), NOT IN returns no rows at all. Chapter 7 explains why and shows the safe NOT EXISTS alternative.

Booleans

is_active is a boolean column, so it already is a condition. WHERE is_active and WHERE is_active = true are equivalent in Postgres, DuckDB and BigQuery. MySQL stores booleans as TINYINT(1) and you will usually see WHERE is_active = 1 there. To keep inactive products, write WHERE NOT is_active.

Check the count both ways

When a filter has several conditions, a quick sanity check is to remove one condition at a time and watch the row count change. If dropping the price limit does not change the count, the limit is not doing anything, which usually means a precedence bug or a wrong value. It takes seconds and catches most AND/OR mistakes before anyone else sees the numbers.

Common mistakes

• Writing category = 'Lighting' AND category = 'Decor'. A single row cannot have two categories, so this always returns nothing. “Lighting and Decor” in plain English usually means OR in SQL.
• Forgetting that < excludes the boundary. “Under $50” means < 50; “$50 or less” means <= 50. When a ticket is vague, ask, or state the choice you made in your reply.