IN vs EXISTS vs JOIN

1. Three ways to answer the same question

IN, EXISTS, and JOIN can all answer "does a match exist in another table?" and get chosen almost interchangeably. Most of the time they return the same rows. The differences only surface in two specific situations: when the matched table can produce more than one match per row, and when it can contain NULL. Both are common in real data, and both produce wrong results silently — no error, just a row count that's wrong in a way nobody notices until someone asks why an author with zero books didn't show up on a report, or why a customer appears twice in a list that was supposed to be deduplicated.

2. The dataset

Verified by actually running this against SQLite — not hand-computed. Two things here are deliberate: Yaa Gyasi has no books at all — she's the target for every "authors with no books" query below — and one book, Untitled Manuscript, has a NULL author_id, an unassigned submission still sitting in the table. That single NULL is enough to break one of these three approaches completely.

CREATE TABLE authors(
  author_id INTEGER,
  author_name TEXT,
  country TEXT
);
CREATE TABLE books(
  book_id INTEGER,
  title TEXT,
  author_id INTEGER,
  published_year INTEGER
);

INSERT INTO authors VALUES
 (1,'Chinua Achebe','Nigeria'),
 (2,'Jane Austen','UK'),
 (3,'Haruki Murakami','Japan'),
 (4,'Toni Morrison','USA'),
 (5,'Gabriel Garcia Marquez','Colombia'),
 (6,'Elena Ferrante','Italy'),
 (7,'Yaa Gyasi','Ghana'),
 (8,'Kazuo Ishiguro','UK');

INSERT INTO books VALUES
 (101,'Things Fall Apart',1,1958),
 (102,'Pride and Prejudice',2,1813),
 (103,'Norwegian Wood',3,1987),
 (104,'Kafka on the Shore',3,2002),
 (105,'Beloved',4,1987),
 (106,'One Hundred Years of Solitude',5,1967),
 (107,'My Brilliant Friend',6,2011),
 (108,'The Remains of the Day',8,1989),
 (109,'Never Let Me Go',8,2005),
 (110,'Untitled Manuscript',NULL,2020);

3. The positive case: "at least one"

"Find authors who have published at least one book." With IN:

SELECT author_name
FROM authors
WHERE author_id IN (SELECT author_id FROM books)
ORDER BY author_name;

IN checks each author_id against the full list of author_ids that appear in books. It doesn't care how many times an author_id repeats in that list — the check is just membership, so each author appears exactly once no matter how many books they've published.

With EXISTS:

SELECT author_name
FROM authors a
WHERE EXISTS (
  SELECT 1 FROM books b WHERE b.author_id = a.author_id
)
ORDER BY author_name;

EXISTS asks a different question for every author: "is there at least one row in books where author_id matches?" It stops looking the moment it finds one match — it doesn't care how many books that author has, only whether the count is greater than zero. Same seven rows, same order, identical output to the IN version.

With a plain JOIN:

SELECT a.author_name
FROM authors a
JOIN books b ON b.author_id = a.author_id
ORDER BY a.author_name;

A JOIN doesn't check membership — it multiplies rows. For every book an author has, JOIN produces one row. Haruki Murakami and Kazuo Ishiguro each have two books, so they each appear twice — nine rows for seven distinct authors, not because the query is "wrong," but because a JOIN was never asking "does a match exist," it was asking "show me every combination that matches." Those are different questions that happen to look similar.

Getting back to one row per author needs an explicit DISTINCT:

SELECT DISTINCT a.author_name
FROM authors a
JOIN books b ON b.author_id = a.author_id
ORDER BY a.author_name;

Forgetting that DISTINCT is the single most common reason a "simple" JOIN-based existence check quietly returns duplicate rows.

4. The negative case: "has none"

"Find authors who have published no books." This should return exactly one row: Yaa Gyasi. With NOT IN:

SELECT author_name
FROM authors
WHERE author_id NOT IN (SELECT author_id FROM books)
ORDER BY author_name;

Not an error — a silent, confidently wrong empty result. The reason is the NULL sitting in Untitled Manuscript's author_id.

With NOT EXISTS:

SELECT author_name
FROM authors a
WHERE NOT EXISTS (
  SELECT 1 FROM books b WHERE b.author_id = a.author_id
)
ORDER BY author_name;

NOT EXISTS asks, per author, "is there no row in books matching this author_id" — a question a stray NULL somewhere else in the table can't interfere with, because the comparison is always scoped to rows that actually match this specific author_id. Correct: one row, Yaa Gyasi.

Equally correct, with a LEFT JOIN:

SELECT a.author_name
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id
WHERE b.book_id IS NULL
ORDER BY a.author_name;

LEFT JOIN keeps every author whether or not books has a match, filling in NULL for book_id when there's no match — filtering on b.book_id IS NULL isolates exactly the authors who never matched. Same single row.

5. Why NOT IN breaks

NOT IN expands to a chain of <> comparisons: author_id <> 1 AND author_id <> 2 AND ... AND author_id <> NULL. Every comparison against NULL in SQL evaluates to UNKNOWN, not TRUE or FALSE — SQL has three truth values, not two. ANDing anything with UNKNOWN produces UNKNOWN, never TRUE, so the moment one NULL enters that chain, the entire condition becomes UNKNOWN for every row being tested — including Yaa Gyasi, who has nothing to do with the NULL book. A WHERE clause only keeps rows that evaluate to TRUE; UNKNOWN is discarded exactly like FALSE. The result is an empty set, with nothing to flag that anything went wrong.

IN doesn't have this failure mode for matching rows, only once it's negated: a genuine match still evaluates to TRUE regardless of what else is in the list, which is why IN worked fine in section 3 against the exact same NULL-containing subquery, and only broke once it became NOT IN.

6. Which one to reach for

EXISTS and NOT EXISTS are the safest default for "does a match exist" questions, for two reasons that hold regardless of the data: they can't produce duplicate rows, because they never return columns from the matched table, only a yes/no per outer row; and they can't be broken by a NULL somewhere else in the matched table, because the comparison is scoped per-row rather than evaluated against the whole subquery result up front.

IN is fine, and often reads more naturally, for the positive case — but shouldn't be negated into NOT IN unless the subquery column is guaranteed NOT NULL. Filtering the NULL out before the comparison happens fixes it:

SELECT author_name
FROM authors
WHERE author_id NOT IN (
  SELECT author_id FROM books WHERE author_id IS NOT NULL
)
ORDER BY author_name;

JOIN is the right tool when the goal is genuinely to pull data from both tables together — not just to check existence. Using it purely as an existence filter, without DISTINCT, is how duplicate rows end up in reports that were never supposed to have them.

7. Cheat sheet

8. Try it yourself

The two gotchas here — JOIN's duplicate rows and NOT IN's silent NULL failure — don't show up in toy examples with clean data. They show up the first time a real dataset has an author with no books, or a record nobody bothered to link to anything. Both are common, and both are one word — DISTINCT, or EXISTS instead of IN — away from not happening.

Practice filtering queries live on TableNotFound →