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
- FROM takes every row of employees.
- WHERE checks the condition
dept_id = 1 AND salary < 90000on each row. - Asha: department 1 is true, but 95000 < 90000 is false. Both must be true for AND, so Asha is dropped.
- Ben, Chen and Hana pass both tests, so they are kept. Everyone outside department 1 fails the first test.
- SELECT then picks the columns from the rows that are left.
- AND is worked out before OR, just as × comes before + in maths. Use brackets whenever you mix them.
Key points
- WHERE runs on each row, before any grouping, and keeps only rows where the condition is true.
- Comparisons: =, <> (or !=), <, <=, >, >=. One = sign, not two.
- AND needs both sides true; OR needs at least one; NOT flips the answer.
- AND binds tighter than OR. Put brackets around every OR that is mixed with AND.
- Text goes in single quotes and must match exactly: 'Sales' is not 'sales' in most databases.
Common mistakes
- Mixing AND and OR without brackets, and getting extra rows.
- Writing
==. SQL uses a single=for comparing. - Writing
WHERE dept_id = 2 OR 3. Each side of OR must be a full condition:dept_id = 2 OR dept_id = 3, or better,dept_id IN (2, 3).
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.