EXISTS performance
- SQL course
- Intermediate
- Lesson 56/115
- 4 min
Learn when EXISTS is faster.
EXISTS performance
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.
Practice EXISTS performance
Find customers with orders on a large orders table using EXISTS.
Starter code
-- fast existence
SELECT name FROM customers c
WHERESample solution
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Expected output
name Ayşe Mehmet
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 When does EXISTS stop?
- At the very end
- Never
- At the first match Correct answer
- When an error occurs
Explanation: EXISTS stops at the first match.
2 Add EXISTS
SELECT name FROM customers c
WHERE ____ (SELECT 1 FROM orders o WHERE o.customer_id = c.id);SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Explanation: EXISTS here uses a correlated subquery that depends on c.id from the outer query.
3 EXISTS performance: match the terms
| Term | Meaning |
|---|---|
EXISTS |
fast |
short-circuit |
first match |
large |
many rows |
IN |
full list |