1The Trick Question
A guaranteed question in any Backend interview is: 'What is the difference between WHERE and HAVING?'. The perfect answer is: 'WHERE filters rows before they are grouped. HAVING filters the groups after the aggregate functions have calculated the math.'
2Step-by-Step Breakdown
The Final Filter. You grouped orders by country. You calculated the SUM(price). Now, the CEO asks: 'Show me ONLY the countries that made MORE than $10,000'.
The WHERE Failure. You CANNOT use the WHERE clause for this. 'WHERE SUM(price) > 10000' will throw a fatal error. Why? Because WHERE runs BEFORE the math happens. It doesn't know the sum yet.
The HAVING Clause. HAVING is literally just a WHERE clause, but it runs AFTER the GROUP BY math has finished. It filters the buckets themselves.
Syntax. 'SELECT country, SUM(price) FROM orders GROUP BY country HAVING SUM(price) > 10000;'. Only countries meeting this threshold are returned.
Knowledge Check. Why does SQL require a separate HAVING clause to filter aggregated math results, instead of just letting you use the WHERE clause?
- →Because WHERE executes before the Math is calculated, so the math results don't exist yet
- →Because WHERE only works on Strings, not Numbers
Combining WHERE and HAVING. They do different jobs. 'WHERE status = 'paid' (filters raw rows) GROUP BY country HAVING COUNT(*) > 50 (filters the math).
HAVING without GROUP BY?. Technically, in some engines, you can use HAVING without GROUP BY if the entire table is treated as one giant group, but it is terrible practice. Always use WHERE for raw rows.
Full Execution Order. Let's review the final order of operations in the database brain: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT.
Aliases in HAVING. In strict SQL, you cannot use SELECT aliases in the HAVING clause (because SELECT runs after). Some forgiving engines (MySQL) allow it, but Postgres requires the full math expression.
Summary. WHERE filters the raw data. HAVING filters the aggregated math.
Level Up 🚀
Advanced cheat sheets, SEO tricks, and interview prep for this topic.
Browser Support
Fully supported.
Fully supported.
Fully supported.
Fully supported.
Accessibility (A11y)
1Clearly Label Which Rows a Filtered Aggregate View Represents
When a UI shows results of a GROUP BY ... HAVING query (e.g. 'countries with revenue over $10,000'), include a visible, screen-reader-announced heading stating the filter threshold, since HAVING silently hides buckets that don't meet the condition and assistive tech users won't otherwise know data was excluded.
SEO Implications
- 1
HAVING Thresholds Affect Which Aggregated Content a Page Actually Shows
A public 'top performers' page built from a GROUP BY ... HAVING query only shows buckets meeting the threshold; if the threshold is too strict, the page may render with very little content, which can look thin to search engines.
Best Practices
Use WHERE for Raw-Row Filters and HAVING Only for Aggregate Filters
Filtering unpaid orders belongs in WHERE, before grouping. Filtering by a computed total like SUM(price) > 10000 belongs in HAVING, since that value doesn't exist until after GROUP BY runs. Mixing them up causes an error or wastes performance.
Repeat the Full Aggregate Expression in HAVING Rather Than Relying on an Alias
Standard SQL and strict engines like Postgres don't allow referencing a SELECT alias inside HAVING, because HAVING logically executes before SELECT. Writing HAVING SUM(price) > 10000 instead of HAVING total > 10000 keeps your query portable across engines.
Frequent Bugs
A query written as 'WHERE SUM(price) > 10000' throws a fatal syntax or column error.
WHERE executes before the GROUP BY aggregation, so the aggregate value doesn't exist yet at that stage. Move the condition into a HAVING clause, which runs after the grouping and aggregation are complete.
A HAVING clause referencing a SELECT alias (e.g. HAVING total > 10000 where total is SUM(price) AS total) fails on strict engines like Postgres.
Repeat the full aggregate expression in HAVING instead of the alias: HAVING SUM(price) > 10000. MySQL is lenient about aliases here, but the full expression works everywhere.
Real-World Examples
Finding High-Revenue Countries for a Sales Report
A sales team wanted a list of only the countries that generated more than $10,000 in total revenue, ignoring smaller markets.
SELECT country, SUM(price) AS total_revenue
FROM orders
GROUP BY country
HAVING SUM(price) > 10000
ORDER BY total_revenue DESC;