✓EXISTS

Semi-join: “is there any match?”

EXISTS (subquery) evaluates to TRUE if the subquery yields at least one row, otherwise FALSE. The engine does not materialize inner columns for the outer result, only the presence of a match matters. Duplicates on the inner side do not multiply outer rows (unlike an INNER JOIN), which is why EXISTS is the idiomatic replacement for IN (SELECT …) when you only need a boolean filter.

The conventional SELECT 1 (or SELECT NULL) inside the subquery is arbitrary, the projection is ignored; only row existence counts.

SQL

Studies with at least one serious adverse event

SELECT s.study_id, s.title
FROM studies s
WHERE EXISTS (
  SELECT 1
  FROM adverse_events ae
  WHERE ae.study_id = s.study_id
    AND ae.severity = 'SERIOUS'
);

✓Illustrative result set

study_id	title
ST-44	NOVA-renal

SQL

NOT EXISTS: anti-join (no matching row)

SELECT p.primer_id
FROM primers p
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.primer_id = p.primer_id
);

✓Illustrative result set

primer_id
P-991
P-992

EXISTS (SELECT COUNT() … WHERE … HAVING COUNT() > 0)

Forcing the inner query to compute counts just to test presence adds aggregation work the semi-join does not need.

EXISTS (SELECT 1 … WHERE …): stop after first row

Express the predicate in the inner WHERE; let the optimizer short-circuit on the first hit.

🧪NOT EXISTS and NULL

If the inner join path can produce NULL keys, three-valued logic still applies inside the subquery.

Pattern: “patients with no qualifying visits”: ensure the inner WHERE either excludes NULL keys or uses predicates that treat unknowns the way compliance expects. When in doubt, compare to NOT IN behavior on nullable columns and prefer NOT EXISTS for clarity.

vs IN (subquery)

IN builds set membership; NULLs in the inner column make NOT IN treacherous. NOT EXISTS usually reads clearer for “no related row matching predicate.”

vs LEFT JOIN … IS NULL

Equivalent for many anti-joins; NOT EXISTS avoids projecting right-side columns and survives refactors where someone adds columns to the SELECT list from the right table.

⚡ Advanced / dialect note

Correlated EXISTS is nested-loop shaped unless the rewriter decorrelates, index the inner columns referenced from the outer row (here, ae.study_id, o.primer_id) the same way you would index a join key.

Correlation budget

Chains of nested EXISTS over large fact tables without selective predicates are a common OLTP regression, capture plans when row counts jump after a release, not only when mean latency moves.

TL;DR

EXISTS (subquery) tests whether the subquery returns at least one row; the engine can stop as soon as it finds a match, unlike COUNT(*) > 0 which may scan more than needed.NOT EXISTS is the idiomatic way to express “no matching row” with sane three-valued logic around NULL, avoiding the pitfalls of NOT IN (subquery) when the inner side can be NULL.