sqlindex

Query

EXISTS vs IN vs JOIN for "has a matching row"

3 min · updated September 2, 2026

“Customers who have placed at least one order” — three equivalent forms, all standard SQL:

-- EXISTS (correlated subquery — stops at the first match)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- IN (subquery)
SELECT * FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o);

-- JOIN + DISTINCT — only reach for this if you're also selecting order columns;
-- otherwise a match with 5 orders duplicates the customer row 5 times before DISTINCT collapses it
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Negate it — “customers with no orders” — and one of the three breaks silently:

-- NOT EXISTS — correct regardless of NULLs in orders.customer_id
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT IN — returns ZERO rows if the subquery contains even one NULL customer_id
SELECT * FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);

-- Fix, if you must use NOT IN: filter the NULL out of the subquery explicitly
SELECT * FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL);

The NOT IN + NULL trap is identical in all six dialects — it’s three-valued logic, not an engine quirk. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. Comparing anything to NULL yields UNKNOWN, and UNKNOWN AND ... can never become TRUE — so every row is excluded, even ones that clearly have no match. NOT EXISTS never has this problem because it doesn’t compare values to the subquery’s rows at all, it just asks whether any row satisfies the join condition. The EXISTS/IN/JOIN syntax itself is the same across MySQL, PostgreSQL, SQLite, BigQuery, Snowflake and DuckDB, and every one of their query optimizers rewrites a correct EXISTS/IN/NOT EXISTS into a semi-join or anti-join internally — so once the NULL trap is handled, reach for whichever form reads clearest, not whichever you assume is “faster.”

← All recipes