EXISTS and NOT EXISTS: Testing for Related Rows

Overview

EXISTS tests whether a subquery returns any rows at all — it doesn't care how many, or what their values are, only whether at least one row matches. Paired with a correlated subquery (one that references a column from the outer query), it answers questions like "does this author have at least one book in the catalog?" without needing a JOIN and without the risk of duplicating the outer row if multiple matches exist.

This is often a better fit than JOIN + DISTINCT for pure existence checks, since EXISTS can stop scanning as soon as it finds one match and never multiplies the outer row.

The Query

Step-by-Step Breakdown

1. FROM authors a — the outer query iterates over authors.
2. WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id) — for each author row, the subquery checks whether any books row references that author's id. The subquery is correlated: a.id comes from the outer row currently being evaluated.
3. SELECT 1 — the actual selected value inside the subquery is irrelevant; EXISTS only cares whether the subquery produces at least one row, so SELECT 1 (or SELECT *) is a common convention.
4. ORDER BY a.name — sort the surviving authors alphabetically.

In this sample dataset, every author has published at least one book, so this query returns all ten authors — which is the expected, correct behavior of EXISTS, not a sign the query is broken.

Variations

Flip the logic with NOT EXISTS to find authors with no books at all:

Common Mistakes

• Selecting specific columns inside the subquery expecting them to matter. EXISTS ignores the subquery's selected columns entirely — only row presence matters, so SELECT 1, SELECT b.id, and SELECT * are all equivalent here.
• Forgetting the correlation. If the subquery doesn't reference the outer row (e.g. WHERE b.author_id = 5 instead of = a.id), EXISTS either matches every outer row or none of them, since the subquery's result no longer depends on which author is being checked.
• Using EXISTS where a JOIN would duplicate rows anyway. If you actually need columns from the related table (not just a yes/no check), a JOIN is usually more appropriate — EXISTS only tells you a match exists, it can't return data from the matched row.
• Confusing NOT EXISTS with NOT IN. They can behave differently when NULL values are involved: NOT IN against a subquery that returns any NULL can unexpectedly return zero rows for the whole query, while NOT EXISTS doesn't have this pitfall.