HAVING vs WHERE in SQL: What's the Difference? (Visual Guide)

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.

The Quick Answer

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;

SELECT city, COUNT(*) AS suspect_count
FROM suspects
GROUP BY city
HAVING COUNT(*) > 3;

How SQL Executes Your Query (Not Top to Bottom)

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.

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:

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: Filtering Individual Rows

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';

SELECT name, age, city
FROM suspects
WHERE age BETWEEN 25 AND 40
  AND city IN ('Miami', 'Tampa');

SELECT name, city, last_seen
FROM suspects
WHERE name LIKE 'J%'
  AND alibi IS NULL;

HAVING: Filtering Groups After Aggregation

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;

SELECT city, AVG(age) AS avg_age
FROM suspects
GROUP BY city
HAVING AVG(age) > 30;

SELECT city,
       COUNT(*) AS suspect_count,
       SUM(bounty) AS total_bounty
FROM suspects
GROUP BY city
HAVING COUNT(*) >= 2
   AND SUM(bounty) > 50000;

Using WHERE and HAVING Together

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;

-- 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)

-- 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

Common Mistakes (and How to Fix Them)

These four mistakes show up constantly in SQL forums and Stack Overflow questions. Understanding them will save you hours of debugging.

Mistake 1: Using aggregate functions in WHERE

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;

-- ✅ Use HAVING for aggregate conditions
SELECT city, COUNT(*)
FROM suspects
GROUP BY city
HAVING COUNT(*) > 3;

Mistake 2: Using HAVING when WHERE would work

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';

-- ✅ Filter early with WHERE
SELECT city, COUNT(*)
FROM suspects
WHERE city = 'Miami'
GROUP BY city;

Mistake 3: Forgetting GROUP BY with HAVING

-- ⚠️ This is valid but rarely useful
SELECT COUNT(*)
FROM suspects
HAVING COUNT(*) > 10;

Mistake 4: Column aliases in HAVING (dialect trap)

-- 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;

Quick Decision Guide: WHERE or HAVING?

Here is a simple framework. If you can describe your filter without using the words “total,” “count,” “average,” or “sum,” you probably want WHERE.

FAQ