Skip to content
Quantoros

What is EXISTS vs IN?

SQLLevel: IntermediateSubqueries & CTEs

What is EXISTS vs IN?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

IN checks if a value matches any value in a list or subquery result. EXISTS checks whether a subquery returns any rows at all.

-- IN: materializes the subquery result, then checks membership
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

-- EXISTS: stops as soon as it finds the first matching row
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.country = 'US');

EXISTS is often faster because it short-circuits. IN can be slow if the subquery returns many rows. Also: IN with NULLs in the subquery can produce surprising results — if the subquery returns any NULL, NOT IN returns no rows at all.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions