Learn the difference between HAVING and WHERE in SQL with visual execution diagrams, code examples, and a quick decision guide. WHERE filters rows, HAVING filters groups.
Jump into the SQLNoir case files and put these tips to work.
WHERE filters individual rows. HAVING filters groups after aggregation. That one sentence covers 80% of the confusion. This guide covers the other 20% with visual diagrams, code examples, and a quick quiz to make sure it sticks.
Both WHERE and HAVING filter data. The difference is when they run and what they filter.
WHERE filters individual rows before any grouping happens. HAVING filters groups after the data has been aggregated. Here are two queries against the same suspects table to show the difference:
SELECT name, city, age FROM suspects WHERE age > 30;
Returns each suspect over 30. Operates on individual rows.
SELECT city, COUNT(*) AS suspect_count FROM suspects GROUP BY city HAVING COUNT(*) > 3;
Returns only cities with more than 3 suspects. Operates on groups.
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups (after GROUP BY) |
| Runs during | Before grouping | After grouping |
| Aggregate functions | Cannot use (SUM, COUNT, AVG) | Can use |
| Requires GROUP BY | No | Yes (almost always) |
| Performance | Faster (reduces data early) | Slower (processes all rows first) |
| Common use | Filter specific records | Filter aggregated results |
WHERE vs HAVING at a Glance
This is the key insight that makes WHERE vs HAVING click. SQL does not execute your query in the order you write it. You write SELECT first, but it runs fifth. Understanding the actual execution order explains exactly why WHERE cannot use aggregate functions and HAVING can.
Pick the table(s)
Filter individual rows
Group remaining rows
Filter groups
Choose columns
Sort results
Cap the output
SQL Query Execution Order: WHERE runs at Step 2, HAVING at Step 4
WHERE runs at step 2, before any grouping. At this point, SQL is looking at individual rows and has no concept of groups or aggregates. HAVING runs at step 4, after GROUP BY has already created groups. That is why HAVING can use COUNT(), SUM(), and AVG(). Those values exist by then.
Here is a query that uses both. The color-coded breakdown shows the execution order vs the order you write it:
city, COUNT(*) AS suspect_countsuspectsage > 25cityCOUNT(*) > 2suspect_count DESCWritten order vs execution order. Notice SELECT is written first but runs fifth.
Understanding execution order is what separates SQL beginners from confident query writers. If you want to build that confidence through practice, SQLNoir's detective cases force you to think through exactly this kind of query logic.
WHERE operates on raw, ungrouped data. It checks each row individually and keeps only the ones that match your condition. Think of it as reviewing case files before the investigation meeting. You eliminate irrelevant suspects early so the rest of the query only processes relevant data.
SELECT name, age, alibi FROM suspects WHERE city = 'Miami';
Returns only suspects located in Miami. Every other city is excluded.
SELECT name, age, city
FROM suspects
WHERE age BETWEEN 25 AND 40
AND city IN ('Miami', 'Tampa');Combines BETWEEN and IN to filter suspects aged 25-40 in two cities.
SELECT name, city, last_seen FROM suspects WHERE name LIKE 'J%' AND alibi IS NULL;
Finds suspects whose name starts with J and have no alibi on record. LIKE and IS NULL are common WHERE operators.
HAVING operates on grouped, aggregated data. It always works with GROUP BY and can use aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(). If WHERE is about filtering individual suspects, HAVING is about deciding which neighborhoods have enough suspicious activity to warrant a full investigation.
SELECT city, COUNT(*) AS suspect_count FROM suspects GROUP BY city HAVING COUNT(*) > 3;
Groups all suspects by city, then keeps only cities with more than 3.
SELECT city, AVG(age) AS avg_age FROM suspects GROUP BY city HAVING AVG(age) > 30;
Calculates the average age per city, then filters to cities where that average exceeds 30.
SELECT city,
COUNT(*) AS suspect_count,
SUM(bounty) AS total_bounty
FROM suspects
GROUP BY city
HAVING COUNT(*) >= 2
AND SUM(bounty) > 50000;Cities with at least 2 suspects AND a combined bounty over $50,000. HAVING supports multiple aggregate conditions.
The most common real-world pattern combines both clauses. WHERE filters rows first, GROUP BY groups them, then HAVING filters the groups. This two-stage approach is both powerful and efficient because WHERE reduces the dataset before the expensive GROUP BY operation.
Imagine you need to find cities where, among suspects over 25 with no alibi, at least 2 people were seen near the crime scene. Here is how that breaks down step by step:
-- Find high-activity cities among suspects -- over 25 who lack an alibi SELECT city, COUNT(*) AS suspect_count FROM suspects WHERE age > 25 AND alibi IS NULL GROUP BY city HAVING COUNT(*) >= 2 ORDER BY suspect_count DESC;
Step 1: WHERE removes suspects 25 and under, plus anyone with an alibi. Step 2: GROUP BY groups the remaining suspects by city. Step 3: HAVING keeps only cities with 2 or more matches.
-- All 20 suspects in the table SELECT * FROM suspects; -- → 20 rows -- After WHERE: age > 25 AND alibi IS NULL SELECT * FROM suspects WHERE age > 25 AND alibi IS NULL; -- → 12 rows (8 eliminated)
•Started with 20 suspects
•WHERE eliminated 8 who were too young or had alibis
•12 individual rows remain for grouping
-- After GROUP BY city + HAVING COUNT(*) >= 2 SELECT city, COUNT(*) AS suspect_count FROM suspects WHERE age > 25 AND alibi IS NULL GROUP BY city HAVING COUNT(*) >= 2; -- → 2 cities remain
•12 rows grouped into 5 cities
•HAVING kept only cities with 2+ suspects
•Final result: 2 cities worth investigating
WHERE reduces rows (20 → 12), then HAVING reduces groups (5 → 2). Two-stage filtering at work.
Think you have got WHERE and HAVING down? Case #003 needs you to filter suspects, group evidence by location, and find patterns in surveillance records. The same two-stage filtering you just learned.
Start InvestigationThese four mistakes show up constantly in SQL forums and Stack Overflow questions. Understanding them will save you hours of debugging.
This is the most common error. WHERE runs before GROUP BY, so aggregates like COUNT() do not exist yet.
-- ❌ This throws an error SELECT city, COUNT(*) FROM suspects WHERE COUNT(*) > 3 GROUP BY city;
•WHERE runs before GROUP BY
•COUNT(*) doesn't exist yet at step 2
•Most databases throw an error here
-- ✅ Use HAVING for aggregate conditions SELECT city, COUNT(*) FROM suspects GROUP BY city HAVING COUNT(*) > 3;
•HAVING runs after GROUP BY
•COUNT(*) is available at step 4
•Query executes correctly
If your filter uses COUNT, SUM, AVG, MIN, or MAX, it belongs in HAVING.
This one does not throw an error. It just runs slower than it should.
-- ⚠️ Works, but inefficient SELECT city, COUNT(*) FROM suspects GROUP BY city HAVING city = 'Miami';
•Groups ALL suspects across ALL cities first
•Then discards every group except Miami
•Wasted work on cities you never needed
-- ✅ Filter early with WHERE SELECT city, COUNT(*) FROM suspects WHERE city = 'Miami' GROUP BY city;
•WHERE eliminates non-Miami rows immediately
•GROUP BY processes only Miami suspects
•Much faster on large datasets
If you're filtering on a regular column value (not an aggregate), use WHERE. It's significantly faster.
-- ⚠️ This is valid but rarely useful SELECT COUNT(*) FROM suspects HAVING COUNT(*) > 10;
Without GROUP BY, the entire table is treated as one group. This checks if the total suspect count exceeds 10. It works, but it is almost never what you want. Add GROUP BY to make HAVING useful.
-- Works in MySQL and SQLite: SELECT city, COUNT(*) AS total FROM suspects GROUP BY city HAVING total > 3; -- Fails in PostgreSQL and SQL Server: -- ERROR: column "total" does not exist -- Use the full expression instead: HAVING COUNT(*) > 3;
MySQL and SQLite let you reference SELECT aliases in HAVING. PostgreSQL and SQL Server require the full aggregate expression. For portable SQL, always use the full expression.
HAVING city = 'Miami') technically works in most databases. But it forces SQL to group ALL rows first, then discard groups. WHERE eliminates rows before grouping, which is significantly faster on large datasets. When in doubt, prefer WHERE.Here is a simple framework. If you can describe your filter without using the words “total,” “count,” “average,” or “sum,” you probably want WHERE.
No → Use WHERE
COUNT, SUM, AVG → Use HAVING
Use WHERE (faster, even with GROUP BY)
3-step decision: No GROUP BY → WHERE. Aggregate filter → HAVING. Column filter → WHERE.
Q1.You need to filter employees where salary is greater than $50,000. Which clause?
Q2.Show departments with more than 10 employees. Which clause?
Q3.Show orders from 2024 where the customer spent over $1,000 total. Which clause(s)?
Q4.Filter cities that start with 'M', then group by city. Which clause for the filter?
Ready to put WHERE and HAVING into practice?
SQLNoir's 6 detective cases start with basic WHERE filtering and scale to complex queries combining JOINs, GROUP BY, and HAVING to crack advanced mysteries. Free, browser-based, no signup required for beginner cases.
Start Your Investigation →Yes, and you should when the situation calls for it. WHERE filters rows before grouping, HAVING filters groups after. Using both is the most efficient pattern: WHERE reduces the dataset early, then HAVING applies aggregate conditions to smaller groups.
Most databases treat the entire result set as one group. So HAVING COUNT(*) > 10 without GROUP BY checks if the total row count exceeds 10. It works but is rarely useful. You almost always want GROUP BY with HAVING.
Because WHERE runs before GROUP BY in SQL's execution order. Aggregates like COUNT() and SUM() only exist after grouping. WHERE has not seen the groups yet. Use HAVING for aggregate conditions.
Generally yes. HAVING processes data after grouping, which requires scanning more rows. WHERE eliminates rows early, reducing the workload for GROUP BY and HAVING. If you can express a filter with WHERE instead of HAVING, WHERE will be faster.
It depends on your database. MySQL and SQLite allow it (e.g., HAVING total_count > 5). PostgreSQL and SQL Server require the full expression (e.g., HAVING COUNT(*) > 5). For portability, use the full aggregate expression.