Why SQL HAVING Is Different from WHERE
When writing SQL queries, two commonly used clauses for filtering data are WHERE and HAVING. At first glance, they may appear to serve the same purpose—filtering rows from a dataset. However, they are used in very different ways and operate at distinct stages of SQL’s internal processing flow.
Understanding the difference between WHERE and HAVING is essential for working with data effectively, especially when dealing with grouping and aggregation operations. This article explores these differences in depth, using practical examples to help clarify when and why you should use each clause.
Learn SQL online
The Purpose of WHERE and HAVING
Both WHERE and HAVING are used to filter data, but they differ fundamentally in terms of what they filter and when they are applied during query execution.
WHEREfilters individual rows before any grouping or aggregation takes place. It works directly on the raw data retrieved from the database.HAVINGfilters aggregated groups of rows, and it only comes into play after the data has been grouped and aggregate functions (likeSUM,AVG,COUNT, etc.) have been computed.
Let’s break this down by first understanding the order in which SQL evaluates different parts of a query.
SQL Query Execution Order
To understand the functional difference, it helps to know the typical order of operations in an SQL query:
FROM– Specifies the tables involved and joins data sources.WHERE– Filters individual rows based on a condition.GROUP BY– Groups filtered rows by specified columns.HAVING– Filters the grouped rows, often using aggregate functions.SELECT– Projects the final columns to include in the output.ORDER BY– Sorts the result set.
This means that WHERE is applied before any aggregation (e.g., SUM, AVG), and HAVING is applied after.
To demonstrate, we’ll use a simple sales table with some fictional data. Here’s a snapshot of the data in the sales table:
| id | region | product | amount |
|---|---|---|---|
| 1 | East | Widget A | 100 |
| 2 | West | Widget B | 200 |
| 3 | East | Widget A | 150 |
| 4 | West | Widget A | 300 |
| 5 | East | Widget B | 50 |
Using WHERE: Filtering Rows Before Aggregation
Suppose we want to calculate the total sales per region, but only for Widget A. Since we’re filtering on a non-aggregated column (product), we use the WHERE clause:
SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE product = 'Widget A'
GROUP BY region;
How This Query Works:
The
WHEREclause filters out all rows whereproductis not'Widget A'.The remaining rows are grouped by
region.The
SUM(amount)is computed within each group.
Result:
| region | total_sales |
|---|---|
| East | 250 |
| West | 300 |
Only rows where product = 'Widget A' are included in the aggregation.
Using HAVING: Filtering Aggregated Results
Now, let’s say we want to see total sales per region, but only include regions where total sales exceed 250, regardless of product.
This requires filtering after aggregation, which is what HAVING is designed for:
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 250;
How This Query Works:
All rows are grouped by
region.For each region, the total sales (
SUM(amount)) is calculated.The
HAVINGclause filters out any group where total sales is less than or equal to 250.
Result:
| region | total_sales |
|---|---|
| West | 500 |
Even though both regions exist in the dataset, only the West region has total sales exceeding 250.
Combining WHERE and HAVING
What if we want to filter on raw data and on aggregated data? For instance, we want to:
Consider only sales of
Widget AGroup by region
Show only those regions where total sales of
Widget Aexceed 250
We can combine both WHERE and HAVING like so:
SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE product = 'Widget A'
GROUP BY region
HAVING SUM(amount) > 250;
Result:
| region | total_sales |
|---|---|
| West | 300 |
This query filters to include only rows where product = 'Widget A', then groups those rows by region, and finally filters the results to include only groups where the total sales amount exceeds 250.
Summary: WHERE vs HAVING
| Feature | WHERE | HAVING |
|---|---|---|
| Filters on | Individual rows | Aggregated groups |
| Applied when | Before grouping and aggregation | After grouping and aggregation |
| Can use aggregate functions? | ❌ No | ✅ Yes |
| Use case | Filter raw data | Filter aggregated results |
| Operates on | Table columns | Output of GROUP BY |
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
