EXISTS with Aggregate Conditions
What you'll learn: How to combine EXISTS with GROUP BY and HAVING to test whether aggregated relationship properties meet specific criteria.
The Core Idea
Sometimes you need to filter rows not just based on whether any related record exists, but whether related records in aggregate meet certain thresholds. For example, finding customers who have placed more than 5 orders, or products that appear in at least 10 different transactions.
By embedding a subquery with GROUP BY and HAVING inside an EXISTS clause, you can test for these aggregate conditions while still benefiting from EXISTS's short-circuit evaluation.
How It Works
The pattern looks like this:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
GROUP BY o.customer_id
HAVING COUNT(*) > 5
);
This finds customers who have more than 5 orders. The subquery groups orders by customer, applies the HAVING filter to the aggregate count, and EXISTS returns true only when that condition is met.
Why This Matters
Think of it as a two-stage filter: first you group related data, then you test whether the summary statistics pass your threshold. This lets you answer questions like "Which departments have an average salary above $80,000?" or "Which suppliers provide more than 20 different products?"
The key advantage over alternatives like joining and grouping in the outer query is clarity and sometimes performance—EXISTS stops checking as soon as it finds a matching group.
Key Takeaway: Use EXISTS with GROUP BY and HAVING to filter based on whether aggregated properties of related records meet specific conditions, combining relationship testing with summary statistics.