Course contentsShow
← Data Science
Lesson 899 of 2,14523. SQL Aggregation and GroupingFree lesson

HAVING vs WHERE: Key Differences

Understand when to use WHERE (filters rows before aggregation) versus HAVING (filters after aggregation).

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(*) > 10 or HAVING 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.