HAVING vs WHERE: Key Differences
What you'll learn: You'll understand exactly when to use WHERE versus HAVING to filter your grouped data correctly.
The Core Difference
Think of data processing like a factory assembly line. WHERE filters raw materials before they enter the production process, while HAVING inspects the finished products after assembly.
In SQL terms:
- WHERE filters individual rows before any grouping or aggregation happens
- HAVING filters groups after aggregation has been performed
When to Use Each
Use WHERE when you need to:
- Filter based on column values in your original data
- Reduce the dataset before expensive grouping operations
- Example:
WHERE order_date >= '2024-01-01'
Use HAVING when you need to:
- Filter based on aggregate function results
- Keep or remove entire groups based on calculated values
- Example:
HAVING COUNT(*) > 10orHAVING AVG(revenue) > 1000
Why This Matters
-- WHERE filters individual orders before grouping
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_amount > 50
GROUP BY customer_id;
-- HAVING filters customer groups after counting
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
You cannot use aggregate functions in WHERE because aggregation hasn't happened yet. You can reference aggregates in HAVING because that's its purpose.
Often you'll use both: WHERE narrows down rows first (improving performance), then GROUP BY aggregates them, then HAVING filters the results.
Key Takeaway: Use WHERE to filter rows before aggregation; use HAVING to filter groups after aggregation. WHERE sees individual records, HAVING sees summary results.