SQLQuokka
Start practicing
QuestionsLearnSandboxFeaturesRoadmapFAQ
SQL Concept

SQL HAVING vs WHERE: What is the Difference?

Both WHERE and HAVING filter, but at different moments. WHERE decides which rows go into the groups; HAVING decides which finished groups come out. Mixing them up is one of the most common SQL interview errors.

Examples run in MySQL 8 and PostgreSQL · Updated

In short

  • WHERE filters rows, before GROUP BY.
  • HAVING filters groups, after aggregates are computed.
  • Aggregates like COUNT(*) are not allowed in WHERE.
  • Use both in one query when you need row and group conditions.

The order of operations

The database runs FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY. When WHERE runs, groups do not exist yet, which is why WHERE COUNT(*) > 2 is an error.

A condition on a plain column (salary > 60000) belongs in WHERE: it is clearer and lets the database discard rows early. A condition on an aggregate (COUNT(*) >= 2) belongs in HAVING.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

employees

emp_idnamedept_idsalarymanager_idhire_date
1Aarav1095000NULL2021-03-01
2Diya108800012022-06-15
3Kabir107200012023-01-10
4Meera2064000NULL2020-11-20
5Rohan205800042024-02-05
6Isha3061000NULL2022-09-12
7Arjun206400042023-07-30
8ZoyaNULL52000NULL2024-05-01

Examples

Filter groups with HAVING

Departments with at least two employees. Marketing and the NULL group have one each, so they are removed after counting.

SELECT dept_id, COUNT(*) AS employees
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY dept_id;
Result
dept_idemployees
103
203

WHERE and HAVING together

WHERE first keeps only salaries above 60,000 (dropping Rohan and Zoya); HAVING then keeps departments with at least two such people.

SELECT dept_id, COUNT(*) AS well_paid
FROM employees
WHERE salary > 60000
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY dept_id;
Result
dept_idwell_paid
103
202

Common mistakes

  • Aggregate in WHERE. WHERE COUNT(*) > 2 fails. Move the condition to HAVING.
  • Row filter in HAVING. Putting salary > 60000 in HAVING changes the meaning or fails, because after grouping there is no single salary. Filter rows in WHERE.
  • Using a SELECT alias in HAVING. PostgreSQL does not allow HAVING employees >= 2 with a SELECT alias; repeat the aggregate (HAVING COUNT(*) >= 2), which works everywhere.

Practise HAVING vs WHERE

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

  1. High-Volume Cities by Completed Trips

    Summarize ride revenue for cities that have enough completed trips.

    Easy✓ Solved

  2. Popular Email Domains

    Count signups per email domain, ignoring letter case, and keep the popular ones.

    Easy✓ Solved

  3. Authors With Five Published Books

    Find authors who have at least five published books in the catalog.

    Easy✓ Solved

  4. Brands With a Premium Range

    Find brands that sell at least two different products at an average price above $3.

    Easy✓ Solved

  5. Customers Buying Both Brands

    Find customers who bought at least one product from each of two brands.

    Easy✓ Solved

  6. Composer Post Rate by App

    Yesterday's share of opened composers that ended in a published post, per app.

    Easy✓ Solved

Want a full set? Work through the GROUP BY & Aggregation study plan.

Questions people ask

Can HAVING be used without GROUP BY?
Yes; the whole table is then one group. It is rare in practice, for example HAVING COUNT(*) > 0.
Which is faster, WHERE or HAVING?
For conditions on plain columns, WHERE, because rows are removed before the grouping work is done.