A subquery inside the WHERE lets a filter decide its own condition instead of hard-coding it. The subquery runs first and it produces a set, and then the outer query compares each row against that set. The mechanics are simple, but the comparison operators have the different set semantics, and the difference between the IN, the NOT IN and the EXISTS is where a surprising number of the broken reports come from.
This is the first lesson in the Subqueries and CTEs module, and the next lesson in this module is about "What Do Subqueries in FROM Buy You?".
How do the subqueries inside the WHERE compare the sets?
The air-quality network in this module has seven monitoring stations and twenty-seven readings. The table stations carries the metadata, which is the city, the type and the install date, and the table readings carries the measurements, one row for each reading day.
CREATE TABLE stations (
station_id INTEGER PRIMARY KEY,
name TEXT,
city TEXT,
installed_on TEXT,
station_type TEXT,
hub_station_id INTEGER
);
CREATE TABLE readings (
reading_id INTEGER PRIMARY KEY,
station_id INTEGER,
recorded_on TEXT,
pm25 REAL,
no2 REAL
);Seven of the stations and twenty-seven of the readings load the network. Two of the stations sit on a main road, and those are the ones that the air-quality people call the traffic sites. One of the readings came in with no PM2.5 value at all, and that is the reading that the trap in this lesson depends on.
INSERT INTO stations (station_id, name, city, installed_on, station_type, hub_station_id) VALUES
(1, 'Harbour Road', 'Bristol', '2019-04-12', 'traffic', NULL),
(2, 'Clifton Hill', 'Bristol', '2020-09-03', 'background', 1),
(3, 'Cathays Terrace', 'Cardiff', '2018-06-21', 'traffic', 1),
(4, 'Roath Park', 'Cardiff', '2021-02-15', 'background', 1),
(5, 'Clyde Walkway', 'Glasgow', '2017-11-08', 'traffic', 1),
(6, 'Kelvingrove', 'Glasgow', '2022-05-30', 'background', NULL),
(7, 'Weir Industrial', 'Glasgow', '2023-01-17', 'industrial', 1);
INSERT INTO readings (reading_id, station_id, recorded_on, pm25, no2) VALUES
(1, 1, '2025-03-01', 18.4, 31.2),
(2, 1, '2025-03-08', 22.7, 28.6),
(3, 1, '2025-03-15', 31.2, 35.4),
(4, 1, '2025-03-22', 27.5, 33.1),
(5, 2, '2025-03-01', 9.8, 15.2),
(6, 4, '2025-03-02', 11.0, 17.6),
(7, 6, '2025-03-05', 7.9, 12.4),
(8, 3, '2025-03-01', 44.6, 38.9),
(9, 3, '2025-03-08', 51.9, 41.2),
(10, 2, '2025-03-08', 11.2, 16.8),
(11, 4, '2025-03-03', 12.0, 18.4),
(12, 5, '2025-03-09', 38.9, 34.7),
(13, 2, '2025-03-15', 8.4, 13.9),
(14, 3, '2025-03-15', 39.2, 35.6),
(15, 5, '2025-03-03', 35.6, 32.4),
(16, 6, '2025-03-12', 9.1, 14.2),
(17, 4, '2025-03-05', 12.2, 18.9),
(18, 5, '2025-03-10', 42.1, 36.8),
(19, 4, '2025-03-16', 14.1, 20.6),
(20, 1, '2025-04-07', 24.9, 29.8),
(21, 2, '2025-04-05', 13.4, 19.7),
(22, 3, '2025-04-06', 38.5, 33.2),
(23, 4, '2025-04-09', 13.9, 21.1),
(24, 5, '2025-04-02', 30.2, 28.9),
(25, 5, '2025-04-11', NULL, 27.4),
(26, 6, '2025-04-03', 8.4, 13.1),
(27, 5, '2025-03-17', 25.4, 24.8);How does the IN compare a row with a set?
The IN asks a membership question. It asks whether the value on the left appears anywhere in the set on the right. That set can be a literal list, or, far more usefully, the result of a query.
"Readings taken at traffic stations" is a question about a different table, and the subquery is how the filter asks it.
SELECT r.reading_id, r.station_id, r.recorded_on, r.pm25
FROM readings r
WHERE r.station_id IN (SELECT s.station_id FROM stations s WHERE s.station_type = 'traffic')
ORDER BY r.reading_id
LIMIT 6;reading_id station_id recorded_on pm25
---------- ----------- ----------- ----
1 1 2025-03-01 18.4
2 1 2025-03-08 22.7
3 1 2025-03-15 31.2
4 1 2025-03-22 27.5
8 3 2025-03-01 44.6
9 3 2025-03-08 51.9The subquery returns the identifiers of the three traffic stations, and the outer query keeps every reading whose station is one of them.
The same predicate that is written with a literal list, which is station_id IN (1, 3, 5), happens to produce the same fifteen readings. But it is a snapshot that goes stale the moment that a station is reclassified. The subquery version re-evaluates, and that is the reason to prefer it.
What if the subquery returns one value?
A subquery that produces a single row and a single column is a scalar, and it can be compared with any of the ordinary operators.
"Show me the readings that were dirtier than the network average" is a comparison against a computed number.
SELECT ROUND(AVG(pm25), 2) AS network_avg_pm25, COUNT(*) AS readings FROM readings;network_avg_pm25 readings
---------------- --------
23.17 27SELECT COUNT(*) AS readings_above_network_average
FROM readings
WHERE pm25 > (SELECT AVG(pm25) FROM readings);readings_above_network_average
------------------------------
12Twelve of the twenty-seven readings exceeded the network mean of the 23.17.
This form is powerful and slightly risky. A scalar subquery that returns no rows yields a NULL, and a scalar subquery that returns more than one row is an error in most of the engines.
When the comparison should be against a set rather than a single number, you use the IN. When it is against one computed number, you use a plain comparison operator.
Why does the NOT IN sometimes return nothing at all?
The NOT IN is the negation of the IN, and the three-valued logic of the SQL makes that negation subtler than it sounds. First, the ordinary case works as you would expect. These are the stations with no reading above the 40 micrograms.
SELECT s.station_id, s.name
FROM stations s
WHERE s.station_id NOT IN (SELECT station_id FROM readings WHERE pm25 > 40)
ORDER BY s.station_id;station_id name
----------- -----------------
1 Harbour Road
2 Clifton Hill
4 Roath Park
6 Kelvingrove
7 Weir IndustrialNow you change the subquery so that it returns a NULL value instead of a set of the identifiers. One of the readings has a missing PM2.5 value, and you select that column to get a single NULL.
SELECT pm25 FROM readings WHERE pm25 IS NULL;pm25
NULLYou feed that set to the NOT IN, and the predicate stops being true for every row.
SELECT COUNT(*) AS stations_returned
FROM stations
WHERE station_id NOT IN (SELECT pm25 FROM readings WHERE pm25 IS NULL);stations_returned
-----------------
0There are zero rows, even though five of the stations have no such reading.
The reason is that x NOT IN (NULL) can never be TRUE. The only possible answer is the UNKNOWN, because the SQL cannot prove that the x differs from a value that it does not know.
One NULL anywhere in the set poisons the entire predicate, and the query returns nothing rather than everything.
What is the safe alternative?
The NOT EXISTS asks a different question. It asks "is there no matching row?" It is immune to the NULL trap, because the rows of the subquery are tested for their existence rather than compared for their equality.
SELECT s.station_id, s.name
FROM stations s
WHERE NOT EXISTS (SELECT 1 FROM readings r WHERE r.station_id = s.station_id)
ORDER BY s.station_id;station_id name
----------- -----------------
7 Weir IndustrialOne station has never reported a reading, and the query says so plainly.
The SELECT 1 inside is deliberate. The value is never read and only the presence of a row matters, so selecting a constant is the clearest way to say that.
The rule to take away is narrow but absolute. You use the NOT EXISTS rather than the NOT IN whenever the subquery might produce a NULL, and you use the EXISTS whenever you are testing for a matching row at all. Both of them run faster than the IN on the large sets, because the engine can stop at the first match.
Quick reference
- The IN compares a value with every member of a set, and that set may come from a subquery or from a literal list.
- A scalar subquery returns one value, and it can be compared with =, >, < and the other friends.
- The NOT IN returns nothing when the set contains a NULL, because the predicate can only be the UNKNOWN.
- The NOT EXISTS tests for the absence of the rows, and it is immune to the NULL trap.
- The EXISTS and the NOT EXISTS short-circuit on the first matching row, so they beat the IN on the large tables.
Set drill: you write two queries that list the stations with no readings at all. One of them uses the NOT IN and the other one uses the NOT EXISTS. Then you say in one sentence why you would never ship the first one.