Filtering with WHERE

"Copy out every card" was a fine start, but almost no real question is about everyone. Who's fourteen? Which books are overdue? Which orders came from Addis? Real questions carry a but only — and SQL's word for but only is WHERE.

The bouncer at the tray

WHERE adds a condition to the query. The librarian still walks the whole drawer, but now holds each card up to your condition before it earns a place on the tray. Passes? On the tray. Fails? Back in the drawer, no hard feelings.

SELECT name, age
FROM students
WHERE age = 14;

name  | age
------+----
Sara  | 14
Amara | 14

Marcus (15) was read, tested, and left behind. Note the reading order you learned last lesson, now with a third step: FROM (which drawer), WHERE (which cards), SELECT (which facts off each surviving card).

The comparisons

The condition is built from a column, a comparison, and a value. Three comparisons carry most of the world's questions:

• = — equals. WHERE age = 14
• > — greater than. WHERE age > 13
• < — less than. WHERE age < 15

(There are cousins — "greater or equal," "not equal" — but they're spellings, not new ideas.)

And here Unit 1's types quietly cash in. Numbers are written bare — age = 14 — but text goes in single quotes:

SELECT name
FROM students
WHERE class = 'Music';

The quotes tell the librarian "this is a value to compare against, not a column name." Forget them, and the database goes hunting for a column called Music — and you get the polite error from last lesson. Meanwhile > and < mean what the column's type says they mean: on numbers, numeric order; on dates, earlier-and-later. That's the payoff of the mislabeled-drawer lesson: because the column declared its kind, the comparison can be trusted.

One more habit that saves beginners real grief: = here is a question ("is it equal?"), never an instruction ("make it equal"). Queries read; nothing in this unit changes a single card.

Stacking conditions

Questions often carry two but onlys. SQL joins them with the words you'd use out loud:

SELECT name
FROM students
WHERE age = 14 AND class = 'Music';

AND demands both; OR is satisfied with either. Fourteen-year- olds in Music: both tests, every card. Students in Music or in Art: either test will do. English and SQL agree here so well that the main advice is: say the question aloud first, then write down what you said.

The empty tray

Ask for age = 40 and you'll get back a table with columns and no rows. That's not an error — it's an answer, and a crisp one: nobody. The librarian checked every card and none passed. Empty results feel like failure at first; they're actually the database at its most honest.

You can now ask for exactly the cards you mean. But they arrive in whatever order the drawer kept them — and the person who asked "youngest first" has opinions about that. Next lesson, the librarian learns to sort the pile before handing it over.