Course contentsShow
← Data Science
Lesson 987 of 2,14525. SQL Subqueries and CTEsFree lesson

EXISTS with Aggregate Conditions

Use EXISTS with subqueries containing GROUP BY and HAVING to test for aggregated relationship properties.

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.