SQL EXISTS and NOT EXISTS
intermediateadvanced
2 min read
What are EXISTS and NOT EXISTS?
EXISTS returns true if a subquery returns at least one row. NOT EXISTS returns true if the subquery returns no rows.
Syntax
SELECT * FROM table1 t1
WHERE EXISTS (
SELECT 1 FROM table2 t2
WHERE t2.fk_id = t1.id
);When to Use
- Checking for the existence of related records
- Anti-joins (finding rows without matches)
- When EXISTS is more efficient than IN
Key Points
- Boolean Check — EXISTS only checks whether the subquery returns any rows.
- SELECT 1 — By convention, use
SELECT 1inside EXISTS since the actual columns do not matter. - Correlated — EXISTS subqueries are almost always correlated.
- NOT EXISTS vs NOT IN — NOT EXISTS handles NULLs correctly. NOT IN fails silently when NULLs are present.
- Performance — EXISTS can short-circuit: it stops as soon as it finds one matching row.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL EXISTS and NOT EXISTS Challenge
Write a query that find students enrolled in at least one course.
Expected result
Only students who have at least one enrollment record.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are EXISTS and NOT EXISTS?
Initializing database...Each run starts from fresh sample data.
More Examples
Products with no orders
Find products that have never been ordered.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is EXISTS faster than IN?
Often yes, especially with correlated subqueries. EXISTS stops at the first match.
Why is NOT EXISTS safer than NOT IN?
NOT IN returns no rows if any value in the subquery is NULL. NOT EXISTS does not have this problem.