11.2 HAVING (vs WHERE)

Level: 11 | Version: v1.1 | Author: Meptrasoft

Overview

HAVING is used to filter groups after GROUP BY.

The key difference is:

WHERE
→ FILTER ROWS

GROUP BY
→ CREATE GROUPS

HAVING
→ FILTER GROUPS

WHERE filters individual rows. HAVING filters grouped results.

SQL WHERE vs HAVING — Rows vs Groups — Figure 1: WHERE filters individual rows before grouping, while HAVING filters groups after aggregate calculations.

Basic HAVING

Using the customers table:

SELECT country, COUNT(*) AS number
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

Result:

country	number
USA	2
UK	2

UAE is removed because its count is only 1.

WHERE vs HAVING

WHERE
→ Filter individual rows
→ Used before GROUP BY

HAVING
→ Filter groups
→ Used after GROUP BY

For example:

SELECT country, COUNT(*)
FROM customers
WHERE age > 20
GROUP BY country
HAVING COUNT(*) > 1;

Here:

WHERE age > 20 filters rows first.
GROUP BY country creates country groups.
HAVING COUNT(*) > 1 filters those groups.

Important Point

WHERE cannot directly filter an aggregate result such as:

WHERE COUNT(*) > 1

Use:

HAVING COUNT(*) > 1

WHERE → Row-level condition HAVING → Group-level condition

Simple Flow

TABLE
 ↓
WHERE
 ↓
GROUP BY
 ↓
AGGREGATE
 ↓
HAVING
 ↓
RESULT

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`WHERE` and `HAVING` do the same thing	WHERE filters rows; HAVING filters groups
HAVING is used before GROUP BY	HAVING filters grouped results
`WHERE COUNT(*) > 1` is valid	Use `HAVING COUNT(*) > 1`
HAVING can only be used with `COUNT()`	It can filter results using aggregate expressions such as `SUM()`, `AVG()`, `MIN()`, and `MAX()`
HAVING filters the original table permanently	It only filters the query result

Placement Quick Points

WHERE
→ FILTER ROWS

GROUP BY
→ CREATE GROUPS

HAVING
→ FILTER GROUPS

WHERE filters individual rows.
GROUP BY creates groups.
Aggregate functions calculate values for each group.
HAVING filters the grouped results.
WHERE cannot directly filter an aggregate calculation such as COUNT(*).

Interview Questions

What is HAVING?

HAVING filters groups created by GROUP BY.

What is the difference between WHERE and HAVING?

WHERE  → Filter rows
HAVING → Filter groups

Why can't WHERE be used with COUNT()?

Because WHERE operates on individual rows before grouping, while COUNT() is calculated for groups. HAVING is used to filter the aggregate result.

Can HAVING be used without GROUP BY?

Some database systems allow it in specific cases, but for beginner SQL, understand HAVING primarily as a group-filtering clause used with aggregation.

Can HAVING use SUM() or AVG()?

Yes.

HAVING SUM(salary) > 100000

Practice & Hands-On Exercises

Using customers:

Find countries having more than one customer:

SELECT country, COUNT(*) AS number
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

Find countries having more than one customer after filtering age > 20:

SELECT country, COUNT(*)
FROM customers
WHERE age > 20
GROUP BY country
HAVING COUNT(*) > 1;

Explain the difference between WHERE and HAVING.
Rewrite a query using HAVING COUNT(*) > 1.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

WHERE
→ FILTER ROWS

GROUP BY
→ GROUP ROWS

HAVING
→ FILTER GROUPS

Use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.

End of HAVING (vs WHERE)