Why might EXISTS outperform a JOIN for existence checks?

Q: Why might EXISTS outperform a JOIN for existence checks?

**Target Audience:** Developers who filter with joins out of habit and want to understand duplicates, semi joins and the plans behind "EXISTS is faster"; beginners who know what a JOIN is. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A club wants to invite every member who has ever booked a court. Two volunteers are given the job. The first volunteer takes the bookings ledger and, for every member, copies out *every* booking they ever made, one line each. Asha booked 40 times, so Asha gets 40 lines. At

The problem

Question presented to candidate: "To list customers who have at least one order, one engineer writes SELECT DISTINCT c.* FROM customers c JOIN orders o ON ... and another writes WHERE EXISTS (SELECT 1 FROM orders o WHERE ...). The second is faster on our big tables. Why might EXISTS beat the JOIN, and is that always true?"

What a strong answer should cover:

A JOIN returns one row per matching pair, so a customer with 40 orders becomes 40 rows (fan-out); DISTINCT or GROUP BY must then remove the duplicates
EXISTS is a semi join: it needs only the first matching row per outer row, can stop there, and never duplicates the outer row
With an index on the foreign key, a nested loop semi join reads one index entry per customer instead of all of them; EXPLAIN ANALYZE shows this as rows=1 versus rows=40 per loop
Fan-out also silently inflates aggregates (COUNT, SUM) when the join is only there to filter
It is not always faster: optimizers often plan IN, EXISTS and even some joins the same way, and a JOIN is correct and fine when you need columns from the other table or the join is on a unique key

Clarifying questions expected:

Do we need any columns from orders, or only the customer rows?
Is there an index on orders.customer_id, and roughly how many orders per customer?

Code / implementation expected: Yes — the JOIN with its duplicates, the EXISTS rewrite, and ideally the two plans side by side.

Schema used by the examples:

exists
performance

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who filter with joins out of habit and want to understand duplicates, semi joins and the plans behind "EXISTS is faster"; beginners who know what a JOIN is. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A club wants to invite every member who has ever booked a court. Two volunteers are given the job.

The first volunteer takes the bookings ledger and, for every member, copie

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLFan-out: a JOIN returns one row per matching order, so Asha appears four times

SQLRows produced: the JOIN makes 8, DISTINCT shrinks them to 3, EXISTS returns 3 directly

PostgreSQL: EXISTS becomes a Nested Loop Semi Join that reads 1 index entry per customer

CREATE TABLE many_customers AS
  SELECT g AS id, 'c' || g AS name FROM generate_series(1, 1000) AS g;
CREATE TABLE many_orders AS
  SELECT g AS id, (g % 500) + 1 AS customer_id FROM generate_series(1, 20000) AS g;
ALTER TABLE many_customers ADD PRIMARY KEY (id);
CREATE INDEX many_orders_customer_idx ON many_orders (customer_id);
VACUUM ANALYZE many_customers;
VACUUM ANALYZE many_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.id, c.name
FROM many_customers AS c
WHERE c.id <= 10
  AND EXISTS (SELECT 1 FROM many_orders AS o WHERE o.customer_id = c.id);

PostgreSQL: JOIN + DISTINCT reads all 40 orders per customer, then de-duplicates 400 rows

CREATE TABLE many_customers AS
  SELECT g AS id, 'c' || g AS name FROM generate_series(1, 1000) AS g;
CREATE TABLE many_orders AS
  SELECT g AS id, (g % 500) + 1 AS customer_id FROM generate_series(1, 20000) AS g;
ALTER TABLE many_customers ADD PRIMARY KEY (id);
CREATE INDEX many_orders_customer_idx ON many_orders (customer_id);
VACUUM ANALYZE many_customers;
VACUUM ANALYZE many_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT DISTINCT c.id, c.name
FROM many_customers AS c
JOIN many_orders AS o ON o.customer_id = c.id
WHERE c.id <= 10;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.