âš¡IntermediateSubqueries
EXISTS and NOT EXISTS
Check for the presence or absence of related rows efficiently using EXISTS.
Overview
EXISTS(subquery) returns TRUE if the subquery returns at least one row, FALSE otherwise. It is more efficient than IN for large datasets because it short-circuits PostgreSQL stops as soon as one matching row is found. NOT EXISTS is the safe alternative to NOT IN when NULLs might be present.
EXISTS is commonly used for "does this customer have orders?", "does this product have reviews?", or any "at least one" check. The SELECT list inside EXISTS is irrelevant EXISTS only cares about whether rows exist, not what they contain (SELECT 1 or SELECT * same result, SELECT 1 is conventional).
Key Points
- •EXISTS(subquery) TRUE if subquery returns ≥ 1 row
- •Short-circuits: stops at first matching row efficient for large datasets
- •The SELECT list inside EXISTS is irrelevant (use SELECT 1 by convention)
- •NOT EXISTS is the NULL-safe alternative to NOT IN
- •Correlated EXISTS: references outer query to check per-row conditions
Examples
EXISTS for existence check
-- Customers who have at least one order
SELECT c.name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- Products that have never been ordered
SELECT p.name FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4