A subquery inside WHERE lets a filter decide its own condition instead of hard-coding it. The subquery runs first and produces a set; the outer query then compares each row against that set. The mechanics are simple, but the comparison operators have different set semantics, and the difference between IN, NOT IN and EXISTS is where a surprising number of broken reports come from.
How do subqueries inside WHERE compare sets?
The air-quality network in this module has seven monitoring stations and twenty-seven readings. The stations table carries the metadata — city, type, install date — and the readings table carries the measurements, one row per 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 stations and twenty-seven readings load the network. Five of the stations report every week and two of them sit on a main road — the ones the air-quality people call traffic sites — and one reading came in with no PM2.5 value at all, which lesson 27's trap 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 IN compare a row with a set?
IN asks a membership question: does the value on the left appear anywhere in the set on the right? The 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.9
The 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 written with a literal list — station_id IN (1, 3, 5) — happens to produce the same fifteen readings, but it is a snapshot that goes stale the moment a station is reclassified. The subquery version re-evaluates, which 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 ordinary operator. "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 27
SELECT COUNT(*) AS readings_above_network_average
FROM readings
WHERE pm25 > (SELECT AVG(pm25) FROM readings);
readings_above_network_average
------------------------------
12
Twelve of the twenty-seven readings exceeded the network mean of 23.17. This form is powerful and slightly risky: a scalar subquery that returns no rows yields NULL, and a scalar subquery that returns more than one row is an error in most engines. When the comparison should be against a set rather than a single number, use IN; when it is against one computed number, use a plain comparison operator.
Why does NOT IN sometimes return nothing at all?
NOT IN is the negation of IN, and SQL's three-valued logic makes that negation subtler than it sounds. First, the ordinary case works as expected — stations with no reading above 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 Industrial
Now change the subquery so that it returns a NULL value instead of a set of identifiers. One reading has a missing PM2.5 value, and selecting that column returns a single NULL:
SELECT pm25 FROM readings WHERE pm25 IS NULL;
pm25
NULL
Feed that set to 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
-----------------
0
Zero rows, even though five stations have no such reading. The reason is that x NOT IN (NULL) can never be TRUE — the only possible answer is UNKNOWN, because SQL cannot prove that x differs from a value 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?
NOT EXISTS asks a different question — "is there no matching row?" — and it is immune to the NULL trap, because the subquery's rows are tested for existence rather than compared for 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 Industrial
One station has never reported a reading, and the query says so plainly. The SELECT 1 inside is deliberate: the value is never read, only the presence of a row matters, and selecting a constant is the clearest way to say that. The rule to take away is narrow but absolute: use NOT EXISTS rather than NOT IN whenever the subquery might produce a NULL, and use EXISTS whenever you are testing for a matching row at all. Both run faster than IN on large sets, because the engine can stop at the first match.
Quick reference
- IN compares a value with every member of a set; the set may come from a subquery or a literal list.
- A scalar subquery returns one value and can be compared with =, >, < and friends.
- NOT IN returns nothing when the set contains a NULL, because the predicate can only be UNKNOWN.
- NOT EXISTS tests for the absence of rows and is immune to the NULL trap.
- EXISTS and NOT EXISTS short-circuit on the first matching row, so they beat IN on large tables.
Set drill: write two queries that list the stations with no readings at all — one using NOT IN and one using NOT EXISTS — and say in one sentence why you would never ship the first one.