EXISTS
Predicate that's true if the subquery returns at least one row. The subquery's SELECT list doesn't matter: SELECT 1, SELECT *, SELECT NULL all behave the same. Pair with a correlated filter (WHERE inner.key = outer.key) for "has related row" and "has no related row" (NOT EXISTS) patterns.
Syntax #
sql
SELECT a.id
FROM a
WHERE EXISTS (
SELECT 1
FROM b
WHERE b.key = a.key
);Example #
Loading SQL editor...
Tip
Prefer NOT EXISTS over NOT IN for exclusion. NOT EXISTS is NULL-safe. It returns rows regardless of whether the correlated column contains NULLs. NOT IN collapses to zero rows the moment the subquery produces a single NULL, with no error. Modern planners (Postgres, DuckDB) generally produce the same plan for NOT EXISTS and LEFT JOIN ... IS NULL, so pick whichever reads more clearly for the intent.