EXISTS & NOT EXISTS
EXISTS tests whether a subquery returns at least one row. NOT EXISTS tests the opposite. They are often more efficient than IN/NOT IN because EXISTS stops as soon as it finds one match (short-circuit evaluation). EXISTS is safe with NULLs, unlike NOT IN.
EXISTS Rules
- EXISTS (subquery) → TRUE if subquery returns at least 1 row
- NOT EXISTS (subquery) → TRUE if subquery returns 0 rows
- EXISTS doesn't care about the value - just whether rows exist
- Convention: write SELECT 1 in the inner EXISTS query (value doesn't matter)
- EXISTS stops scanning after finding the first match - often faster than IN
- NOT EXISTS is NULL-safe (unlike NOT IN)
EXISTS Examples
-- Users who placed at least one order (EXISTS)
SELECT u.name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Result: John Doe, Alice Smith
-- Users who NEVER ordered (NOT EXISTS - safer than NOT IN)
SELECT u.name FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Result: Bob Lee
-- Orders that have a payment
SELECT o.id, o.status FROM orders o
WHERE EXISTS (
SELECT 1 FROM payments p WHERE p.order_id = o.id
);
-- Orders with NO payment yet
SELECT o.id, o.status, o.total_amount FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM payments p WHERE p.order_id = o.id
);
-- Use in DELETE: delete users with no orders
DELETE FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Quick Quiz
Tip
Tip
Practice EXISTS NOT EXISTS in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with EXISTS NOT EXISTS is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of EXISTS NOT EXISTS from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- EXISTS tests whether a subquery returns at least one row.
- EXISTS (subquery) → TRUE if subquery returns at least 1 row
- NOT EXISTS (subquery) → TRUE if subquery returns 0 rows
- EXISTS doesn't care about the value - just whether rows exist