Tech Blogger: Difference between WHERE and HAVING clause

Sunday, 30 May 2021

This post explains the difference between WHERE and HAVING clauses and how to use them with example scenarios.

WHERE:

WHERE clause is used to filter only the desired records in the table.
Records that don't meet the condition are removed prior to the grouping operation.

HAVING clause is used as a secondary filter. If the value is calculated in your query and the value is not readily available in your table then you need use the HAVING clause.
This filter is applied after your aggregate functions are executed in order to meet the mentioned HAVING condition from your resultant.

For example, let's consider you have a data set with ticket sales details of a game by team.

Scenario 1 :

The resultant here will be each team’s sum of sales from every game that had sales greater than 50,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team;

Scenario 2 :

The resultant here will be only teams that have total sales of greater than 5,00,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

Scenario 3 :

The resultant here will be only teams that have grossed more than 5,00,000 from games that did more than 50,000 in sales.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

WHERE VS HAVING:

Features	WHERE	HAVING
Syntax	WHERE condition;	GROUP BY columns HAVING condition;
Example	WHERE sales_amt > 50000;	GROUP BY team HAVING SUM(sales_amt ) > 500000;
What value can be used condition	Direct value from table	Derived aggregate value from table
When to use WHERE/HAVING clause	To filter data based on values available in the table.	To filter data based on aggregate values derived from values available in the table