What is EXISTS?
- SQL course
- Intermediate
- Lesson 53/115
- 4 min
Check whether a subquery returns any row.
What is EXISTS?
EXISTS checks whether a subquery returns at least one row. It returns true or false.
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);This query returns customers with orders.
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.
What is EXISTS? — details
EXISTS is a correlated subquery. It stops at the first matching row, so it is fast.
It doesn't matter which columns the subquery returns; that's why SELECT 1 is usually written and only the existence of a row is checked.
Practice What is EXISTS?
Find customers with orders using EXISTS.
Starter code
-- has orders
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 What does EXISTS return?
- A value
- A number
- A table
- True or false Correct answer
Explanation: EXISTS returns true or false.
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 is true when the subquery returns at least one row; it finds customers with orders.
3 What is EXISTS: match the terms
| Term | Meaning |
|---|---|
EXISTS |
existence |
subquery |
inner query |
true |
yes |
false |
no |