EXISTS and NOT EXISTS
Often you only need to know whether a matching row exists, and you do not need to read it. EXISTS and NOT EXISTS answer that directly, and NOT EXISTS is the safe way to ask what is missing.
How it works
EXISTS checks each outer row against a subquery. It is TRUE when the subquery finds at least one row for that outer row, and it never returns NULL. NOT EXISTS is TRUE when the subquery finds none.
WHERE EXISTS (SELECT 1 FROM b WHERE b.key = a.key)keeps the rows of a that have a match in b. The column list in the subquery does not matter.- The condition that ties the subquery to the outer row, here
b.key = a.key, is what makes it a per-row check. Without it,EXISTSasks whether b has any rows at all. NOT EXISTSkeeps the rows with no match. It gives the same rows asLEFT JOIN ... IS NULL.value NOT IN (subquery)returns no rows at all when the subquery contains aNULL.NOT EXISTShas no such trap.
Common mistake
Leaving out the correlation condition turns EXISTS into a check on the whole subquery, so it is either TRUE for every row or FALSE for every row. If you get everything or nothing, look at that condition first.
Worked example
SELECT m.make
FROM (SELECT DISTINCT make FROM `learn.mot.dim_vehicle`) AS m
WHERE EXISTS (
SELECT 1
FROM `learn.mot.fact_vehicle_summary` AS s
WHERE s.make = m.make AND s.defect_stats.dangerous_defects > 0
)
ORDER BY m.make
LIMIT 5The outer query lists each make once. For each make, the subquery looks for a vehicle summary of that make with a dangerous defect.
A make stays in the result when that search finds at least one row.