What is the difference between WHERE and HAVING?

The main difference between WHERE and HAVING in SQL is when and on what they filter data, and this often comes up in interviews, so it’s important to answer clearly with an example.

1. WHERE Clause:

• Used to filter rows before any grouping or aggregation occurs.
• Works on individual rows in a table.
• Cannot be used with aggregate functions like SUM(), COUNT(), etc.

Example:
Suppose we have a Sales table, and we want all sales where the amount is greater than 1000:

SELECT * FROM Sales
WHERE Amount > 1000;

Here, the filtering happens row by row before any grouping.

2. HAVING Clause:

• Used to filter groups after aggregation.
• Works with aggregate functions like SUM(), COUNT(), AVG().
• Cannot filter individual rows directly.

Example:
If we want to find products whose total sales exceed 5000:

SELECT ProductID, SUM(Amount) as TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(Amount) > 5000;

Here, the filtering happens after the GROUP BY, on the aggregated result.

Challenges I’ve faced:

• One common mistake is trying to use WHERE with aggregate functions, which throws errors. For example, WHERE SUM(Amount) > 5000 is invalid. Understanding the logical order of SQL execution—FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY—helps avoid this.

Practical application:
I’ve applied HAVING when generating sales reports to filter top-performing products or regions and WHERE when filtering raw transactional data before any summary or aggregation.

Limitation:

• HAVING can be slightly slower than WHERE because it filters after aggregation, so it processes more data.
• Alternative: If possible, pre-filter data with WHERE before aggregation to improve performance.