Filtering rows with WHERE

Almost every real query, update and delete targets a subset of rows chosen by WHERE.

WHERE filters rows before anything else happens to them. Only rows for which the condition is true make it into the result:

SELECT id, total FROM orders WHERE status = 'shipped';

Comparison operators are =, <> (the standard "not equal"; != also works in every major database), <, <=, > and >=. Note the single =: SQL has no ==. String comparison is exact and, in PostgreSQL, case-sensitive: 'london' does not equal 'London'. MySQL's default collations compare case-insensitively, a common surprise when moving between the two.

Combine conditions with AND, OR and NOT. AND binds tighter than OR, exactly like multiplication and addition, so this query does not do what it seems to say:

-- shipped orders over 50, OR any order at all from customer 1
WHERE status = 'shipped' AND total > 50 OR customer_id = 1

Add parentheses whenever you mix AND and OR; it costs nothing and removes the doubt.

Because WHERE runs before the select list is computed, you cannot refer to a column alias defined in SELECT. Repeat the expression, or wrap the query in a subquery, which you will see later.

The same WHERE clause appears in UPDATE and DELETE, where getting it wrong changes the wrong rows. A good habit: write and run the SELECT version first, check the rows, then turn it into the modifying statement.

Example

SELECT id, customer_id, total
FROM orders
WHERE status = 'shipped' AND total > 50;
--  id  | customer_id | total
--  101 |           1 |  68.50
--  105 |           2 | 238.00

-- Parentheses make the intent explicit
SELECT id, status
FROM orders
WHERE customer_id = 1 AND (status = 'pending' OR total < 100);
--  id  | status
--  101 | shipped
--  102 | pending

-- Aliases from SELECT are NOT visible in WHERE:
-- SELECT total * 1.2 AS gross FROM orders WHERE gross > 100;  -- ERROR
SELECT id, total * 1.2 AS gross FROM orders WHERE total * 1.2 > 100;

Remember

WHERE keeps rows whose condition is true
AND binds tighter than OR; parenthesise mixed logic
SELECT aliases cannot be used in WHERE

Watch out for

Forgetting parentheses around an OR, so the filter matches far more rows than intended.

Check yourself

Which rows does WHERE status = 'shipped' OR status = 'pending' AND total > 100 match?
Why does SELECT total * 2 AS dbl FROM orders WHERE dbl > 100 fail?

Official docs: www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WHERE

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks