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.