WHERE is where reports earn their keep: it is the clause that turns a whole table into the handful of rows a question is actually about. A WHERE keeps a row only when the predicate after it — one condition evaluated per row — comes out true, and everything else quietly leaves the result.
What is the data being filtered?
A filter needs something to filter. The bookshop's staff roster lives in a single table of eight rows: who works here, what they do, what they earn, which branch they work from, and — for most of them — the manager they report to. The manager_id column points back at the same table, so the roster documents its own reporting line:
CREATE TABLE staff (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
role TEXT NOT NULL,
department TEXT NOT NULL,
salary INTEGER,
city TEXT,
email TEXT UNIQUE,
manager_id INTEGER REFERENCES staff(id)
);
INSERT INTO staff (id, name, role, department, salary, city, email, manager_id) VALUES
(1, 'Priya Raman', 'Buyer', 'Buying', 61000, 'London', '[email protected]', NULL),
(2, 'Dana Kowalski', 'Buyer', 'Buying', 46500, 'Manchester', '[email protected]', 1),
(3, 'Aisha Zahra', 'Cafe Lead', 'Cafe', 32000, 'Leicester', '[email protected]', NULL),
(4, 'Marcus Bell', 'Bookseller', 'Sales', 28000, 'London', '[email protected]', 5),
(5, 'Lena Marsh', 'Sales Manager', 'Sales', 54000, 'London', '[email protected]', NULL),
(6, 'Tom Whitfield', 'Bookseller', 'Sales', 27500, 'Leeds', '[email protected]', 5),
(7, 'Joe Naylor', 'Barista', 'Cafe', 25500, 'Manchester', '[email protected]', 4),
(8, 'Ruth Adeyemi', 'Seasonal Bookseller', 'Sales', 21000, NULL, '[email protected]', 5);
Two gaps in that roster are deliberate, because the rest of the module leans on them. Ruth's city is still empty — the seasonal bookseller has not been told which branch she will cover — and three rows carry no manager_id at all, because the two department heads and the cafe lead answer to nobody. Every result in this lesson follows from those two holes in an otherwise tidy spreadsheet.
What is a predicate, physically?
A predicate is any expression that is true or false for a single row. Applied to a bookshop staff roster, the filter lives before projection and selects rows: London first, because a city promotion is being planned:
SELECT name, department, city FROM staff WHERE city = 'London';
name department city
----------- ---------- ------
Priya Raman Buying London
Marcus Bell Sales London
Lena Marsh Sales London
Three things deserve attention in that output. The predicate ran row by row — the engine did not scan for the string London once globally, it tested city = 'London' eight times, each with a different row's value. Comparison against text is exact and case-sensitive here: London matches, london does not (lesson 7 returns for partial and case-insensitive matches). And the roster actually held eight rows — the seasonal bookseller whose city is still unknown never appeared, because a NULL city is neither equal nor unequal to London. That silent exit is correct SQL behaviour, and lesson 8 gives it a full treatment.
How do comparisons differ between numbers and text?
Numbers compare the way school maths taught you, ordering inclusive at the edges:
SELECT name, salary FROM staff WHERE salary >= 30000 AND salary <= 55000;
name salary
------------- ------
Dana Kowalski 46500
Aisha Zahra 32000
Lena Marsh 54000
The two-range style — a low bound and upper bound joined by AND — reads fine but is exactly what BETWEEN does in one breath; hold that thought for lesson 7. Text such as a department name compares as a single unit, so equality is the tool:
SELECT name, role, salary FROM staff WHERE department = 'Sales';
name role salary
------------- ------------------- ------
Marcus Bell Bookseller 28000
Lena Marsh Sales Manager 54000
Tom Whitfield Bookseller 27500
Ruth Adeyemi Seasonal Bookseller 21000
A trap particular to numbers worth naming now: quoting the number — '30000' — makes a text literal. Engines vary in how leniently they coerce quoted numerals back into numbers, so filters can behave differently between databases for no visible reason. Write predicates that compare like with like.
Which comparison operators does a filter have to hand?
Six operators cover almost every real predicate. The not-equal pair is the only non-obvious one: engines accept both; I write != because no entity encoding ever has to defend it, and note the standard spelling for completeness — see the comments in the query below.
SELECT name, city FROM staff WHERE department != 'Sales';
-- the same predicate, standard spelling: department <> 'Sales'
name city
------------- ----------
Priya Raman London
Dana Kowalski Manchester
Aisha Zahra Leicester
Joe Naylor Manchester
| Operator | Reads as | Typical use |
|---|---|---|
| = | equals | exact matches on codes, departments, ids |
| != or the two-keyword spelling | not equal | everything except one value |
| < > | strictly less or greater | thresholds, debugging data ranges |
| <= >= | inclusive bounds | slabs such as salary bands, dates since |
When do you want exactly one row back?
Primary keys give predicates a superpower: filter on the key and at most one row can survive. Pulling the whole seasonal worker row shows why full-row inspection matters when debugging — the NULL in the city cell is the fixed roster's way of showing a branch that was never assigned, and it turns the missing-data story from a hypothesis into a fact:
SELECT * FROM staff WHERE id = 8;
id name role department salary city email manager_id
-- ------------ ------------------- ---------- ------ ---- --------------------------------- ----------
8 Ruth Adeyemi Seasonal Bookseller Sales 21000 NULL [email protected] 5
How do you verify a filter without trusting it?
Every predicate invites exactly two sanity checks. Run the count both ways — full table versus filtered — and the proportion tells you whether the predicate is doing what you assumed; then invert the predicate once and eyeball the survivors. Salaries below the bank's legal minimum, for instance, need one comparison and produce one candidate:
SELECT name, role FROM staff WHERE salary < 25000;
name role
------------- -------------------
Ruth Adeyemi Seasonal Bookseller
Verifying with the inverse keeps boundary bugs honest: not below 25000 matched seven people, and one glance confirms the reverse set contains nobody your first query claimed. When I audit someone else's filter, that inverse check is the first thing I run — it costs a few seconds of compute, and it catches swapped bounds and inverted comparisons, which are the two defects that otherwise reach dashboards wearing plausible numbers.
Quick reference
- WHERE evaluates one predicate per row and keeps only the true survivors.
- Text comparisons are exact; numeric literals belong unquoted for portable filters.
- NULL values never pass an equality test — absence is its own lesson topic further into this module.
- Filter by primary key when exactly one row is the goal.
Your turn: from the staff roster, find everyone above 30000 and show the salary along with role, then the set whose department is not Buying, and finally the one row where the salary is exactly 54000. For each, say out loud — before you run anything — how many rows you expect, then make the engine grade your answer.