SQL / SELECTING ROWS
Filtering rows with WHERE
Filter a result set with WHERE so only rows whose condition is true come back, and explain why NULL rows vanish from both sides of a test.
What you will learn
- Place WHERE right after FROM and let it keep only rows whose test returns true
- Read any WHERE clause as a test rerun for every row, with that row's values in it
- Predict which rows disappear because the test came out unknown instead of false
- Filter on a column you never put in the SELECT list
Understanding Filtering rows with WHERE
WHERE is a test applied to one row at a time. The database takes the rows that FROM hands it, substitutes that row's values into the WHERE expression, evaluates it, and keeps the row only if the answer is true. Nothing about the row changes: WHERE decides membership, never content, so it can shrink how many rows you get but never how many columns or what they hold.
The part beginners trip over is that a comparison has three possible answers, not two. True keeps the row and false drops it, but a comparison against NULL answers unknown, and unknown is discarded exactly like false. That is why temp_c > 5 and temp_c <= 5 do not add back up to the whole table when some temp_c is NULL: the NULL row fails both tests, so it is missing from both results without any warning.
WHERE also has a fixed position in the order the database works through your query: FROM builds the rows, WHERE filters them, and only then is the select list computed. Two practical consequences fall out of that order. You may filter on a column you never return, because at filter time the whole row is still in hand, and you may not reference an alias defined in the select list, because that alias does not exist yet when the filter runs.
CREATE TABLE reading (
station TEXT,
taken_on TEXT,
temp_c INTEGER
);
INSERT INTO reading VALUES
('north', '2026-01-04', 12),
('north', '2026-01-05', -1),
('south', '2026-01-04', 21),
('south', '2026-01-05', NULL),
('east', '2026-01-04', 4);
SELECT station, temp_c
FROM reading
WHERE temp_c > 5;WHERE is a truth test evaluated once per row, and a row survives only when that test comes out true.
Worked examples
Filtering on a column you do not return
Runs against the reading table above and tests taken_on while returning only station.
SELECT station
FROM reading
WHERE taken_on = '2026-01-05';Example explained
Line 1WHERE taken_on = '2026-01-05' reads a column that never appears in the select list, because the filter sees the stored row, not the trimmed-down output row.
Line 2Single quotes make '2026-01-05' a text literal; taken_on is stored as text here, so both sides compare as text.
Line 3The south row whose temp_c is NULL is kept, since the test touches taken_on only and a NULL elsewhere in the row is irrelevant.
Line 4Two rows come back with no duplicate removal; if north had two rows on that date, north would be listed twice.
Testing an expression, and why the alias cannot help
Shows the same expression written in both the select list and the filter, against the reading table above.
SELECT station, temp_c * 2 AS doubled
FROM reading
WHERE temp_c * 2 > 20;Example explained
Line 1temp_c * 2 is spelled out twice on purpose: WHERE runs before the select list, so the name doubled does not exist yet and WHERE doubled > 20 would fail with an unknown-column error.
Line 2The multiplication is evaluated once per row for the test, then again for the column that is returned.
Line 3For the row with temp_c NULL, NULL * 2 is NULL, so the comparison answers unknown and the row is dropped.
A test that matches nothing
Demonstrates that a WHERE clause no row satisfies is a valid query, not an error.
SELECT station, temp_c
FROM reading
WHERE station = 'west';Example explained
Line 1station = 'west' is false for all five rows, so every row is discarded and an empty result is returned.
Line 2An empty result and a failed query are different outcomes: the column headers still describe what the query would have returned.
Line 3Zero rows here means the data holds no such station, not that the syntax is wrong, which is why row counts are worth reading before you start rewriting the filter.
Important notes
WHERE can only judge one row at a time, so a condition about a whole group, such as a count per station, cannot go here; that belongs in HAVING after grouping.
Filtering says nothing about ordering: the surviving rows may come back in any order until you sort them explicitly.
Common mistakes
Referring to a select-list alias in the filter, as in SELECT temp_c * 2 AS doubled ... WHERE doubled > 20; the query fails with an unknown-column error because WHERE is evaluated before the alias is created.
Assuming a test and its opposite cover the whole table, so temp_c > 5 followed by temp_c <= 5 is used as a complete split; rows whose temp_c is NULL show up in neither result and are silently lost.
Wrapping a text value in double quotes, as in WHERE station = "north"; engines that follow the standard read that as a column name and report that no column named north exists.
Try it yourself
Change, predict, then run
Using the reading table from the main example, return station and taken_on for rows where temp_c < 5, then change the test to temp_c >= 5 and run it again. Confirm the two runs return four rows in total rather than all five, and identify which row is missing and why.
Open the SQL workspaceCheck your understanding
The reading table holds five rows and one of them has a NULL temp_c. How many rows does SELECT station FROM reading WHERE temp_c <> 21 return?
- Three rows: the ones holding 12, -1 and 4
- Four rows: every row except the one holding 21, since a NULL is certainly not 21
- Five rows, because <> only rejects an exact match and NULL is not an exact match
- Two rows: 12 and 4, because a negative value fails a numeric comparison
Show answer
Three rows survive. NULL <> 21 evaluates to unknown rather than true, and WHERE keeps a row only when the test is true, so the NULL row is discarded along with the row that actually holds 21. Four rows is the tempting answer because in ordinary language a missing temperature is obviously not 21, but SQL never claims a NULL differs from a value; it refuses to answer, and an unanswered test drops the row.