What is the difference between WHERE and HAVING?

Suppose a college wants to know which departments have an average mark above 75, counting only final-year students.

First you go through the pile of mark sheets and throw out everyone who is not in final year. That is WHERE: it looks at one sheet at a time.

Then you sort the remaining sheets into piles by department and work out each pile's average. Finally, you throw away the piles whose average is 75 or less. That is HAVING: it looks at whole piles, after grouping.

Table marks:

SELECT department, AVG(score) AS avg_score, COUNT(*) AS students
FROM marks
WHERE year = 4                 -- row filter: before grouping
GROUP BY department
HAVING AVG(score) > 75;        -- group filter: after grouping

Result:

Karan was removed by WHERE (not final year). ECE was removed by HAVING (average 72).

Trying to use an aggregate in WHERE fails:

SELECT department FROM marks WHERE AVG(score) > 75 GROUP BY department;
-- ERROR: aggregate functions are not allowed in WHERE

The database processes a query in a logical order that is different from the order you write it:

1. FROM and JOIN: get the rows
2. WHERE: filter rows
3. GROUP BY: form groups
4. Aggregates are computed (SUM, AVG, COUNT)
5. HAVING: filter groups
6. SELECT: pick the output columns
7. ORDER BY, then LIMIT

Since WHERE runs before any group exists, there is no SUM or AVG for it to check. HAVING runs after aggregates are calculated, so it can use them.

• Customers who placed more than five Swiggy orders last month: WHERE order_date >= ... then GROUP BY customer_id HAVING COUNT(*) > 5.
• Finding duplicate emails in a users table: GROUP BY email HAVING COUNT(*) > 1.
• Cities whose total UPI transaction value crossed a threshold in a report.

• Putting a plain row condition in HAVING, such as HAVING year = 4. Many databases reject it unless year is in GROUP BY, and even when allowed it filters late, after doing all the grouping work. Row conditions belong in WHERE.
• Using a SELECT alias in HAVING (HAVING avg_score > 75). MySQL accepts this, but standard SQL, PostgreSQL and SQL Server do not. Repeat the expression instead.
• Thinking HAVING needs GROUP BY. Without GROUP BY, the whole table is treated as one group, so SELECT COUNT(*) FROM marks HAVING COUNT(*) > 3 is valid.

• Can you use HAVING without GROUP BY?
• Why can't you use a column alias from SELECT in WHERE?
• How would you find duplicate rows in a table?