Difference Between WHERE and HAVING

Question

Accepted Answer

WHERE and HAVING both filter data in a SQL query, but they act at different stages of query execution -- WHERE filters individual rows before grouping happens, while HAVING filters groups after GROUP BY has produced them.

Key Points:
• WHERE cannot reference aggregate functions like SUM() or COUNT() because aggregation hasn't happened yet at that point.
• HAVING is evaluated after GROUP BY, so it can filter based on the result of an aggregate function.
• Using WHERE to filter rows before grouping is more efficient than filtering afterward, since it reduces the number of rows the database has to aggregate.
• A query can use both clauses together: WHERE narrows the raw rows, then HAVING narrows the resulting groups.

Example:
To find departments with more than 5 employees, you group employees by department and then use HAVING COUNT(*) > 5, since COUNT(*) is an aggregate that only exists after grouping.

Code Example:
```
SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) > 5;
```

Interview Tip:
A concise interview answer is:

"WHERE filters individual rows before grouping and can't use aggregate functions, while HAVING filters the groups produced by GROUP BY and can reference aggregates like COUNT() or SUM(). I typically use WHERE to cut down rows early for performance, then HAVING to filter on the aggregated result."