EXISTS performance

Learn when EXISTS is faster.

EXISTS stops at the first match, so it is fast on large tables. The subquery does not fully run.

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

This query stops at the first order found.

name is selected from customers c. WHERE keeps only the rows where EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) holds.

EXISTS performance — details

EXISTS short-circuits. IN generates the full list. On large data EXISTS is preferred.

An index on the matching column in the subquery (e.g. orders.customer_id) makes EXISTS noticeably faster.

Find customers with orders on a large orders table using EXISTS.

Show example solution

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

Add EXISTS

SELECT name FROM customers c
WHERE ____ (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Show answer

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