Pick the Right Rows

WHERE tests one row at a time

A WHERE clause is a question asked of each row on its own. Rows that answer true come back; rows that answer false do not. Nothing else about the query changes — the columns you asked for are still the columns you get.

When a filter has two parts, the word joining them decides everything:

• AND — keep the row only if both parts are true. Adding an AND can only ever make the result smaller.
• OR — keep the row if at least one part is true. Adding an OR can only ever make it bigger.

That sounds obvious written down, and it still catches people constantly, because English is sloppier than SQL. "Show me the garden tools and the cheap ones" means OR. "Show me the cheap garden tools" means AND. The same two conditions, joined two different ways, by a sentence that barely changed.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, habitat TEXT, seen INTEGER);
INSERT INTO birds (species, habitat, seen) VALUES
  ('wren', 'hedge', 12),
  ('heron', 'water', 1),
  ('moorhen', 'water', 9),
  ('dunnock', 'hedge', 3);

SELECT species FROM birds WHERE habitat = 'water' OR seen < 5;

SELECT species FROM birds WHERE habitat = 'water' AND seen < 5;

species
-------
heron
moorhen
dunnock
(3 rows)

species
-------
heron
(1 row)

Three rows, then one. The OR version collected everything that was either a water bird or rarely seen; the AND version kept only the row that was both.

The third answer: unknown

SQL comparisons do not return only true and false. Compare anything with NULL — which means "no value recorded" — and the answer is unknown, and WHERE keeps only rows that came out true.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, seen INTEGER);
INSERT INTO birds (species, seen) VALUES ('wren', 12), ('nightjar', NULL), ('dunnock', 3);

SELECT species FROM birds WHERE seen < 5;

SELECT species FROM birds WHERE seen >= 5;

SELECT species FROM birds WHERE seen < 5 OR seen IS NULL;

species
-------
dunnock
(1 row)

species
-------
wren
(1 row)

species
--------
nightjar
dunnock
(2 rows)

Two filters that look like opposites returned one row each, out of three rows. The nightjar has no count, so it is not known to be under five and not known to be five or more either; it falls out of both. Only the third query, which says so explicitly with IS NULL, brings it back.

The table in the editor has a row like that, and the answer you are working towards correctly leaves it out. Knowing why it is out — rather than not noticing — is the point.

Your turn

The editor holds a seven-row tools table and a query that is meant to answer one question: which garden tools have a deposit under 1000?

Press Run first. Five rows come back. Among them are the hand saw, which is not on the garden shelf at all, and the post driver at 1100, which is well over the limit — neither belongs in the answer.

Change one word in the WHERE line so the query returns only the rows where both conditions hold, and run it again. You should get two rows, cheapest first.

Then try putting the OR back and reading the five-row result again, to see exactly which rows each version lets through.

If something goes wrong

If you get seven rows, the WHERE line has probably been deleted. Without it a SELECT returns every row.

If you get a syntax error pointing at a < or a >, the comparison operators have been written the wrong way round: they are <= and >=, with the angle bracket first.

If you get three rows rather than two, the donated ladder may have been brought back with an OR deposit IS NULL. That is a perfectly good clause, and it is not what this question asks for.

Nothing here can break. The database is built fresh and thrown away on every run.