EXISTS vs IN Performance
When EXISTS short-circuits and outperforms IN, a frequent senior-screening question.
EXISTS vs IN Performance is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What EXISTS Actually Tests
EXISTS takes a subquery and returns true the moment that subquery produces at least one row. It does not care about the values returned — only whether any row exists.
- It is a boolean test, used in
WHERE. - It is almost always correlated: the inner query references the outer row.
This single-question screen appears in nearly every mid-to-senior SQL interview.
A Basic EXISTS Query
Find customers who have placed at least one order. The inner query is correlated by o.customer_id = c.id; EXISTS returns true as soon as one matching order is found.
Note SELECT 1 — the projected value is irrelevant, so most engineers write 1 or *. Interviewers accept either; the optimizer ignores the select list inside EXISTS.
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);Short-Circuit Behavior
The key word interviewers want is short-circuit. EXISTS stops scanning the inner query the instant it finds one matching row. It never needs to build or deduplicate the full list of matches.
IN, by contrast, conceptually materializes the set of values from the subquery and then checks membership. For large or duplicate-heavy inner sets, that difference matters.
The Same Query With IN
Here is the IN equivalent of the customers-with-orders query. Logically identical result, different mechanics: the subquery is uncorrelated and produces a list of customer ids that the outer query checks against.
On modern optimizers these often produce the same plan — but on large duplicate-heavy orders, EXISTS can win because it stops at the first hit.
SELECT c.name
FROM customers c
WHERE c.id IN (
SELECT o.customer_id FROM orders o
);NOT EXISTS Beats NOT IN
This is the punchline of the whole lesson. NOT EXISTS is the safe way to express an anti-join. Unlike NOT IN, it is not broken by NULLs in the inner query.
This reliably finds every customer with no orders, even if orders.customer_id contains NULLs.
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);Why NOT EXISTS Is NULL-Safe
NOT EXISTS only asks did the correlated subquery find any matching row? — a clean yes/no. A NULL customer_id simply never satisfies o.customer_id = c.id, so it neither matches nor poisons the logic.
Contrast with NOT IN, where a NULL in the list forces an UNKNOWN and drops all rows. This is the reason senior interviewers prefer NOT EXISTS for anti-joins.
When IN Is Actually Better
Be balanced — IN is not always worse. When the subquery returns a small, static, distinct list, IN is clear and fast:
- A handful of literal values, or a tiny lookup table.
- An uncorrelated query the optimizer can run once and cache.
The query below is perfectly idiomatic; reaching for EXISTS here would be over-engineering.
SELECT name
FROM products
WHERE category_id IN (
SELECT id FROM categories WHERE active = true
);The Honest Modern Answer
Mature optimizers (Postgres, recent SQL Server and MySQL) frequently rewrite IN and EXISTS into the same semi-join plan. So for plain positive membership, performance is often identical.
The differences that still matter:
NOT INvsNOT EXISTS— correctness with NULLs (real, not just speed).- Very large or unindexed inner tables — EXISTS short-circuits.
EXISTS vs JOIN for Existence
Another framing interviewers raise: why not just JOIN? A join that only checks existence can multiply rows if the right side has duplicates, forcing a DISTINCT. EXISTS never duplicates the outer row.
So for a pure existence check, EXISTS is cleaner than JOIN ... DISTINCT. Use a join when you actually need columns from the other table.
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;Indexing Makes or Breaks It
The performance answer is incomplete without indexes. A correlated EXISTS runs the inner lookup per outer row, so an index on the correlated column — here orders(customer_id) — is what makes it fast.
Mentioning "I'd index the join column the subquery correlates on" turns a textbook answer into a practical one interviewers respect.
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);Interview Soundbite
Say: "EXISTS is a correlated boolean test that short-circuits on the first matching row, while IN checks membership in a value list. For positive checks, modern optimizers often produce the same semi-join plan. The real difference is NOT EXISTS vs NOT IN: NOT EXISTS is NULL-safe, so I prefer it for anti-joins — and I make sure the correlated column is indexed."
Quick Check
The crux of the EXISTS vs IN discussion.
Recap
EXISTS vs IN, settled:
EXISTSis a correlated boolean that short-circuits on the first matching row; the select list inside is irrelevant.INchecks membership in a value set and is great for small, distinct, uncorrelated lists.- For positive checks, modern optimizers often pick the same semi-join plan.
- Prefer
NOT EXISTSoverNOT INfor anti-joins — it is NULL-safe. Index the correlated column.
That wraps the Subqueries Deep Dive course.
Frequently asked questions
Is the “EXISTS vs IN Performance” lesson free?
Yes — the full text of “EXISTS vs IN Performance” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “EXISTS vs IN Performance”?
When EXISTS short-circuits and outperforms IN, a frequent senior-screening question. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “EXISTS vs IN Performance” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.