WHERE: keeping only the rows you want

WHERE keeps only the rows for which a condition is true, using comparisons such as =, <>, <, > and the words AND, OR and NOT.

Why it matters

Tables hold every row ever stored, and you almost always want a slice: one department, orders over £100, everyone hired this year. WHERE is how you say which rows count.

The idea

A condition is a statement about a row that is either true or false, like salary > 60000. The database checks it against every row and keeps the rows where it is true.

You can combine conditions: AND needs both to be true, OR needs at least one, and NOT flips true and false. Comparisons are =, <> (not equal; != also works), <, <=, > and >=. Text values go in single quotes.

A bouncer with a list of rules. Every row walks up to the door; if it passes the rules, it gets in. "Over 18 AND on the guest list" is stricter than "over 18 OR on the guest list".

A bouncer answers yes or no. SQL has a third answer, unknown, which comes from NULL — and WHERE treats unknown the same as no. The NULL lesson next in this unit shows why that matters.

Syntax

SELECT columns
FROM table_name
WHERE condition AND (other OR another);

How it works

  1. FROM takes every row of employees.
  2. WHERE checks the condition dept_id = 1 AND salary < 90000 on each row.
  3. Asha: department 1 is true, but 95000 < 90000 is false. Both must be true for AND, so Asha is dropped.
  4. Ben, Chen and Hana pass both tests, so they are kept. Everyone outside department 1 fails the first test.
  5. SELECT then picks the columns from the rows that are left.
  6. AND is worked out before OR, just as × comes before + in maths. Use brackets whenever you mix them.

Key points

Common mistakes

Not true: WHERE dept_id = 2 OR 3 keeps the rows of departments 2 and 3.
In fact: Each side of OR must be a full condition: dept_id = 2 OR dept_id = 3, or shorter, dept_id IN (2, 3).

In the interview

WHERE filters rows before any grouping: the database evaluates the condition for each row and keeps only those where it is true — false and unknown are both dropped. I combine conditions with AND, OR and NOT, and because AND has higher precedence than OR, I always bracket OR conditions when mixing them, so the query says exactly what I mean.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.