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.

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 5

The 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.

All BigQuery lessons