EXISTS or IN? The Right Answer Runs 30 Times Faster

Two queries, same result. One takes 0.1 seconds, the other takes 3. The difference is a single keyword.

The problem

Find customers who have placed at least one order. All three approaches are correct; their cost is not.

Three ways

-- 1. IN
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- 2. EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 3. JOIN + DISTINCT
SELECT DISTINCT c.* FROM customers c
JOIN orders o ON o.customer_id = c.id;

The core difference

EXISTS stops at the first matching row. A customer with 500 orders still costs one row. IN has to materialise the entire inner list before comparing.

JOIN + DISTINCT is the worst: it multiplies out 500 rows and then deduplicates them.

Common mistakes

1. NOT IN meets NULL

-- Returns NOTHING if the subquery yields one NULL
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Always safe
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

2. Writing SELECT * inside EXISTS

SELECT 1 and SELECT * perform identically — the optimiser ignores the column list. But SELECT 1 states the intent: “I am not fetching data, I am checking.”

3. Forgetting the correlation

-- True for every customer, because the subquery ignores c
WHERE EXISTS (SELECT 1 FROM orders);

EXISTS must be a correlated subquery — the inner query has to reference the outer table.

Practice

• LeetCode 1050 — Actors and Directors Who Cooperated At Least Three Times
• LeetCode 1148 — Article Views I
• LeetCode 1517 — Find Users With Valid E-Mails
• LeetCode 619 — Biggest Single Number