SQL WHERE vs HAVING: filter rows or groups
WHERE filters individual rows before grouping. HAVING filters groups after totals or other aggregate values are calculated. Compare both with a sales example.
By QueryPlan. Published . Updated .
On this page
Start with the source rows
The report should show regions with at least 100 in total sales. The source rows show individual sales, while the report needs a total for each region. Both queries use the same source data and return columns named region and total. Their result rows differ because they filter at different stages.
Swipe or scroll the table to see all columns.
| region | amount |
|---|---|
| East | 60 |
| East | 50 |
| West | 120 |
| West | 20 |
| North | 30 |
Schema and sample SQL
CREATE TABLE sales (region TEXT NOT NULL, amount INTEGER NOT NULL);INSERT INTO sales VALUES ('East',60),('East',50),('West',120),('West',20),('North',30);Before: WHERE filters rows
The WHERE condition keeps only an individual sale whose amount is at least 100. That leaves the West sale of 120. The later sum has only that one input row, so it reports West with 120. East disappears because neither 60 nor 50 meets the row condition. West also loses its smaller sale of 20 before its total is calculated.
SELECT region, SUM(amount) AS total FROM sales WHERE amount >= 100 GROUP BY region ORDER BY region;Swipe or scroll the table to see all columns.
| region | total |
|---|---|
| West | 120 |
After: HAVING filters groups
The HAVING query sums every sale in each region before checking the total. East totals 110 and West totals 140, so both regions qualify. North totals 30 and is excluded. The query returns the requested regional totals. Moving the filter changes the result, so the queries cannot be treated as interchangeable performance choices.
SELECT region, SUM(amount) AS total FROM sales GROUP BY region HAVING SUM(amount) >= 100 ORDER BY region;Swipe or scroll the table to see all columns.
| region | total |
|---|---|
| East | 110 |
| West | 140 |
What the comparison tells you
Add South sales of 40 and 60 in the Visualizer to check the threshold. HAVING includes South with a total of exactly 100 because the comparison is >= 100. WHERE excludes both sales before grouping. An empty sales table returns no rows in either grouped query.
Choose the condition from the report you need. For a report about large individual sales, the WHERE version can be appropriate. For a report about region totals, use the aggregate condition in HAVING. You can also filter input rows and groups in one query when the question requires both. Check the source rows and expected report before comparing performance.
Check your understanding
Can South reach a total of 100 from sales of 40 and 60 if neither sale passes amount >= 100?
Show the explanation
The HAVING query sums both sales and includes South at exactly 100. The WHERE query removes both rows before grouping, so South has no result row.