WHERE vs. HAVING: Filtering Data

The Fundamental Rule and a Common Mistake

As a quick refresher, the core difference is:

• WHERE filters individual rows.
• HAVING filters groups created by GROUP BY.

This distinction arises directly from the sequence in which the database engine executes a query. Let's start by watching a short video that clearly explains the difference and demonstrates the common error of trying to use an aggregate function in a WHERE clause.

The video correctly points out that when the WHERE clause is being processed, the aggregated value (like a COUNT) "hasn't even been calculated yet." To truly understand this, we need to look at the full logical order of operations.

The Logical Order of Query Execution

This is the "under the hood" model that explains so much of SQL's behavior. The order in which you write a query (lexical order) is not the order in which the database processes it (logical order).

This diagram, which we briefly saw last lesson, clearly contrasts the two. Notice the positions of WHERE, GROUP BY, and HAVING in the execution order on the right.

Now, let's watch a detailed walkthrough that explains this execution pipeline step by step. This directly addresses the "why" you're interested in.

As the video demonstrates, the logical execution order is (simplified):

1. FROM / JOIN: Identifies the tables and joins them to create a working set of data.
2. WHERE: Filters individual rows from this working set based on specified conditions. No aggregations are available at this stage.
3. GROUP BY: Arranges the remaining rows into groups.
4. HAVING: Filters these newly created groups based on conditions, which can now include aggregate functions (SUM, COUNT, etc.) calculated over the groups.
5. SELECT: Processes the final list of columns to be returned.
6. ORDER BY: Sorts the final result set.

Understanding this sequence makes the roles of WHERE and HAVING perfectly clear.

Combining WHERE and HAVING for Precise Control

The real power comes from using both clauses in a single query to apply two layers of filtering.

• Use WHERE to pre-filter individual rows you don't need before any grouping occurs. This is efficient.
• Use HAVING to post-filter the groups based on a summary calculation.

Scenario: From a Sales table, find product categories where we only consider individual sales over $50, and where the total sales for that category still exceed $10,000.

SELECT
  ProductCategory,
  SUM(SaleAmount) AS TotalCategorySales
FROM Sales
WHERE SaleAmount > 50         -- Step 1 (Execution): Filter individual rows first.
GROUP BY ProductCategory      -- Step 2 (Execution): Group the remaining rows.
HAVING SUM(SaleAmount) > 10000; -- Step 3 (Execution): Filter the aggregated groups.

This query is both correct and efficient:

1. The WHERE clause first throws away all individual sales of $50 or less.
2. The GROUP BY clause then works on a smaller, pre-filtered set of rows.
3. Finally, the HAVING clause filters the resulting groups to find the high-value categories.

For more examples and a detailed read on this topic, the following article is a great resource.

Performance Implications: Filter Early, Filter Often

As the DataCamp article notes, using WHERE to filter is almost always more performant than using HAVING for a condition that could apply to individual rows.

Consider this inefficient query:

-- Inefficient Query
SELECT
  ProductCategory,
  COUNT(*)
FROM Sales
GROUP BY ProductCategory
HAVING ProductCategory = 'Electronics'; -- Filtering after grouping

And compare it to the efficient version:

-- Efficient Query
SELECT
  ProductCategory,
  COUNT(*)
FROM Sales
WHERE ProductCategory = 'Electronics' -- Filtering before grouping
GROUP BY ProductCategory;

Both produce the same result, but the second query is much faster on a large table. It tells the database to find only the 'Electronics' rows first, and then it performs the GROUP BY on that much smaller subset of data. The first query forces the database to group all sales by category and then throw away all groups except one. This is a perfect example of how understanding the low-level execution order leads to better code.

SELECT
  service_name,
  AVG(request_time_ms) AS avg_error_time
FROM service_logs
WHERE status = 'ERROR'
GROUP BY service_name
HAVING AVG(request_time_ms) > 500;

Conclusion

You've now taken a deep dive into the distinction between WHERE and HAVING. This is one of the most fundamental and frequently tested concepts in SQL, and mastering it moves you well beyond a basic understanding.

Key Takeaways:

• The distinction between WHERE and HAVING is determined by the logical order of query execution.
• WHERE filters rows before GROUP BY. It cannot use aggregate functions. Use it to filter as early as possible for better performance.
• HAVING filters groups after GROUP BY. It is specifically designed to work with aggregate functions.
• You can and should use both in the same query to apply precise, multi-level filtering.

This summary image provides a final, clear comparison.

Preview of the Next Lesson:
Now that you have a firm grasp of aggregation (GROUP BY) and the two types of filtering (WHERE and HAVING), you're ready to create more sophisticated summary reports. In the next lesson, we will explore how to combine multiple aggregate functions in a single query to calculate several metrics at once.