Difference Between WHERE and HAVING

WHERE	HAVING
Filters rows before grouping or aggregation.	Filters groups after aggregation.
Used with SELECT, UPDATE, DELETE	Used only with GROUP BY (aggregated queries).
Cannot use aggregate functions (SUM(), COUNT(), etc.) in WHERE.	Can use aggregate functions in HAVING.
Acts on individual rows.	Acts on grouped rows (results of GROUP BY).

---

How to Think About It

• WHERE → filter rows first.
• GROUP BY → group the filtered rows.
• HAVING → filter the groups after aggregation.

---

Example to Make It Clear

Imagine a table Sales:

sale_id	customer_id	amount
1	101	500
2	101	200
3	102	700
4	103	150

---

WHERE Example (filter rows)

Find sales over $300:

SELECT * FROM Sales
WHERE amount > 300;

Result:

sale_id	customer_id	amount
1	101	500
3	102	700

• WHERE filters individual rows based on amount.

---

HAVING Example (filter groups)

Find customers whose total sales are over $500:

SELECT customer_id, SUM(amount) AS total_sales
FROM Sales
GROUP BY customer_id
HAVING SUM(amount) > 500;

Result:

customer_id	total_sales
101	700
102	700

• HAVING filters groups — customers with total sales over 500.
• SUM(amount) — an aggregate function — can’t be used in WHERE, but can be used in HAVING.

🔑 Key Points

• WHERE filters before aggregation — on rows.
• HAVING filters after aggregation — on groups.
• WHERE can’t use SUM(), COUNT(), etc.
• HAVING can use aggregate functions.

---

Another Analogy

Think of SQL like:

1. WHERE = “Clean the raw ingredients.”
2. GROUP BY = “Group them together.”
3. HAVING = “Throw out the bad groups after cooking.”

Simple Example

Step	What Happens
WHERE amount > 300	Pick rows where amount is more than 300.
GROUP BY customer_id	Group those rows by customer.
HAVING SUM(amount) > 500	Keep only groups where total sales > 500.