Yogesh Chauhan's Blog

The Differences Between HAVING Clause and WHERE Clause in SQL

in SQL/MySQL on January 2, 2020

REMEMBER the difference:

We can limit the groups in a result set by adding HAVING condition afterwords. 

We can limit it before grouping them using WHERE.

Learn more about HAVING in this article:

Why do we need HAVING Clause in SQL?

Learn more about WHERE in this article:

Some Useful Operators in The SQL WHERE Clause

Now, let's just go through some examples to understand the difference better. We will go through search query that uses HAVING and WHERE one by one.

Let's look at the search query that uses HAVING clause:


//Search using HAVING Clause

SELECT Seller_name, COUNT(*) AS Invoice_quantity, 
ROUND(AVG(invoice_total),2) AS Invoice_average 
FROM sellers JOIN invoices 
ON sellers.seller_id=invoices.seller_id 
GROUP BY seller_name 
HAVING AVG(invoice_total) > 500
ORDER BY invoice_quantity DESC;

In this query, we are just pulling up seller names, number of invoices using count() function and getting the average of invoices. After that we are joining the sellers table with invoices table and using group by to group the sellers together.

Now, after that I have used HAVING clause so that means the query groups the sellers together and then checks for the condition. If the AVG for invoice total is greater than 500 then and then the sellers will show up in the search results set.

Let's look at the search query that uses WHERE clause:


//Search using WHERE Clause

SELECT Seller_name, COUNT(*) AS Invoice_quantity, 
ROUND(AVG(invoice_total),2) AS Invoice_average 
FROM sellers JOIN invoices 
ON sellers.seller_id=invoices.seller_id 
WHERE invoice_total > 500
GROUP BY seller_name 
ORDER BY invoice_quantity DESC;

In this query I am fetching the same data and then joining the same tables. But immediately after joining the tables, I have added WHERE clause, before the Group By clause. So, that means it limits the invoices included in the groups if the invoices are not greater than $500.

In other words we can say that the search condition in WHERE was being applied to every row but in HAVING, it was applied to the group of rows.

Also there are few more differences between them.

Notice the first query. We are using HAVING with AVG.

We can't use aggregate functions with WHERE clause but we can use it with Having clause. The reason is because we put WHERE before Group By so it won't limit the grouped rows.

Another difference is that HAVING clause can ONLY refer to columns included in the SELECT statement. While, WHERE can refer to any column.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create a simple slider with CSS and jQuery?CSSWhat are Identifiers in JavaScript?JavaScriptHow states work in React?ReactSolution to pod install fails with json error on Mac OS X 10.15 (or Mac OS Catalina)MiscellaneousHow to Clone Objects in PHP?MiscellaneousWhat are “holes” in a React component?React