What does the following demonstrate about the EXISTS vs IN performance trade-off? — Query A: WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000) — Query B: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000)

Question

SQL Fundamentals — Hard

What does the following demonstrate about the EXISTS vs IN performance trade-off? — Query A: WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000) — Query B: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000)

Accepted Answer

EXISTS short-circuits after finding the first match per outer row, making it more efficient when the subquery returns many rows. IN, on the other hand, evaluates the full subquery first.