Having Vs Where

With this article, we are going to discuss about SQL HAVING Vs WHERE clause. These two clauses almost do the same job but for a different query. Often times, most developers are confused when to use Having clause not the WHERE clause. The WHERE clause is the most frequently used and almost anyone is the WHERE clause expert. Knowing the difference between these two clause helps make your query efficient and saves time.

I have been asked the difference between these two in almost any interviews I have attended in the past. Similarly, this is also my first question when I take database interview and you will be amazed how many do not know the difference even the resume claims over a decade of SQL/Database experience.

WHERE: This clause filters the result on SELECT, INSERT, UPDATE and DELETE SQL Statements.

HAVING: This clause filters the result on SELECT clause using AGGREGATE functions like GROUP BY. In a simple term, the  HAVING clause applies filter on aggregate data set.

Below, I will present our readers with a series of SQL examples that demonstrate the difference between Having and Where clause. These examples automatically makes you the HAVING  expert.

WHERE Clause:

SELECT * 
FROM   employees 
WHERE  lastname = 'Davolio'; 

Even a cave man know the result to this query therefore no explanation is required.

AGGREGIATE Function:

SELECT COUNT(customername) as Total,
       country 
FROM   customers 
GROUP  BY country 
ORDER  BY country DESC;

Output:
Total Country
4 Venezuela
13 USA
7 UK
2 Switzerland
2 Sweden
5 Spain
2 Portugal
1 Poland
1 Norway
5 Mexico
3 Italy
1 Ireland
11 Germany
11 France
2 Finland
2 Denmark
3 Canada
9 Brazil
2 Belgium
2 Austria
3 Argentina

The above query displays employee count by country. After seeing the result, you are only interested to find the country list that have at-least 10 or more employee. How do you add filter to the above result sets?

HAVING Clause:

SELECT COUNT(customername) as Total,
       country 
FROM   customers 
GROUP  BY country 
HAVING Total >= 10
ORDER  BY country DESC;

Result:
Total Country
13 USA
11 Germany
11 France

The above query displays the number of employee for each county and displays the count and country where employee count is above 9. Now, you are asked to ignore Germany  from the result even though it have over 9 employees because it is not in our reporting zone.

WHERE & HAVING Clause In One Query:

SELECT Count(customername) AS Total, 
       country 
FROM   customers 
WHERE  country NOT IN ( 'Germany' ) 
GROUP  BY country 
HAVING total > 10 
ORDER  BY country DESC;

Result:
Total Country
13 USA
11 France

The above query uses Where and Having Clause in a single query. The WHERE Clause selects everything except Germany and then aggregates it. The Having clause then filters and display the aggregate data for employee count over 9.

Having and Where is pretty simple and easy to understand after following the above examples. You now know the difference and next time, I want you to use it and answer it out loud during interview.