NOT EXISTS
- SQL course
- Intermediate
- Lesson 54/115
- 4 min
Find rows that fail a condition.
NOT EXISTS
NOT EXISTS checks that the subquery returns no rows. It replaces an anti-join.
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);This query returns customers without orders.
name is selected from customers c. WHERE keeps only the rows where NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = … holds.
NOT EXISTS — details
With NOT EXISTS you find 'missing' records. It is an alternative to LEFT JOIN ... IS NULL.
For example, WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) finds customers who never ordered.
Practice NOT EXISTS
Find customers without orders using NOT EXISTS.
Starter code
-- no orders
SELECT name FROM customers c
WHERESample solution
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Expected output
name Fatma Ali
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add NOT
SELECT name FROM customers c
WHERE ____ EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Explanation: NOT EXISTS is true when the subquery returns no rows; it finds customers without orders.
2 What does NOT EXISTS find?
- Rows that have no match Correct answer
- Rows that have a match
- All of them
- One of them
Explanation: NOT EXISTS finds missing records.
3 NOT EXISTS: match the terms
| Term | Meaning |
|---|---|
NOT EXISTS |
missing |
EXISTS |
existence |
anti-join |
absent |
NULL |
empty |