In this article, we will look at the difference between the WHERE and HAVING Clause in SQL. This is an important question asked in Interviews so we will look at each Keyword in detail with sample queries and compare their differences. At first, let us look at the description of the WHERE and HAVING Clause.
Table of Contents
Introduction
Let’see about quick introduction for where and having clause in SQL.
Where Clause
The WHERE Clause is typically used to filter records or extract rows from a table which satisfy a given condition. It can be used with a Single Table or while using JOIN between two or more tables. It can be used with SELECT, DELETE and UPDATE Statements.Having Clause
The HAVING Clause is used to filter records from Groups rather than a Table. The Groups satisfying the condition given in the HAVING Clause will only appear in the result set. Moreover, it can be only used with the SELECT Statement.
Further reading:
Working example to demonstrate difference between Where and having in SQL
Now, to differentiate between the two in practice, we consider a Table PHARMACY_PURCHASES which contains records of purchases of products in a Pharmacy as shown below:
| PURCHASE_ID | PRODUCT_NAME | QUANTITY | SALE_AMOUNT |
| 1 | CROCIN | 1 | 500 |
| 2 | PARAXIN | 2 | 1200 |
| 3 | CROCIN | 1 | 500 |
| 4 | CETZINE | 2 | 800 |
| 5 | CROCIN | 4 | 2000 |
| 6 | BENADRYL | 2 | 1400 |
| 7 | PARAXIN | 3 | 1800 |
Let’s define the schema to create the above table and enter the sample records:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE PHARMACY_PURCHASES ( PURCHASE_ID INTEGER, PRODUCT_NAME VARCHAR2(20), QUANTITY INTEGER, SALE_AMOUNT INTEGER ); INSERT INTO PHARMACY_PURCHASES VALUES(1,'CROCIN',1,500); INSERT INTO PHARMACY_PURCHASES VALUES(2,'PARAXIN',2,1200); INSERT INTO PHARMACY_PURCHASES VALUES(3,'CROCIN',1,500); INSERT INTO PHARMACY_PURCHASES VALUES(4,'CETZINE',2,800); INSERT INTO PHARMACY_PURCHASES VALUES(5,'CROCIN',4,2000); INSERT INTO PHARMACY_PURCHASES VALUES(6,'BENADRYL',2,1400); INSERT INTO PHARMACY_PURCHASES VALUES(7,'PARAXIN',3,1800); |
Now, let’s assume we want to write a query to Retrieve Products Purchased in Pharmacy having their Total Sales Greater than 2000. For this, we will use the GROUP BY Clause to Group The Products with the Sum of their respective Sale Amount. To calculate Sum, we will use the SUM() Aggregate Function. Now, to get products having Sales greater than 2000, we will use the HAVING Clause with the given condition. The query will be:
|
1 2 3 4 5 6 |
SELECT PRODUCT_NAME,SUM(SALE_AMOUNT) AS TOTAL_SALES FROM PHARMACY_PURCHASES GROUP BY PRODUCT_NAME HAVING SUM(SALE_AMOUNT) > 2000; |
Output:
We cannot write this query using the WHERE Clause as it does not work with Aggregate Functions in SQL such as COUNT, SUM, AVG, etc. If we use the WHERE Clause instead of HAVING we get Syntax Error.
So this is one Primary Difference that WHERE Clause filters rows with only a given condition and cannot be used with Aggregates whereas the HAVING Clause can be used with conditions as well as with Aggregates.
Note:The WHERE Clause can use only Single Row function likeUPPER(),LOWER()etc., and not Multiple Row Functions like above.
Now, suppose we want to Retrieve Purchase Details like Total Quantity and Total Sales Amount of products PARAXIN,CROCIN and CETZINE. We can use both the WHERE and HAVING Clause to write a query that will give the records based on the condition above.
Using WHERE Clause:
|
1 2 3 4 5 6 7 |
SELECT PRODUCT_NAME,SUM(QUANTITY) AS TOTAL_QUANTUTY, SUM(SALE_AMOUNT) AS TOTAL_SALES FROM PHARMACY_PURCHASES WHERE PRODUCT_NAME IN ('CROCIN','PARAXIN','CETZINE') GROUP BY PRODUCT_NAME; |
Using HAVING Clause:
|
1 2 3 4 5 6 |
SELECT PRODUCT_NAME,SUM(QUANTITY) AS TOTAL_QUANTITY, SUM(SALE_AMOUNT) AS TOTAL_SALES FROM PHARMACY_PURCHASES GROUP BY PRODUCT_NAME HAVING PRODUCT_NAME IN ('CROCIN','PARAXIN','CETZINE'); |
The Two queries will give the same output:
Let us look at how both queries are different in performance.
The query using WHERE Clause, At first filters the rows with the given PRODUCT_NAME, then groups them according to their PRODUCT_NAME and performs the aggregates for each column. So WHERE Clause filter records before Aggregate Calculation are done.
Whereas the query using HAVING Clause, At first groups the products according to their name, performs the aggregates on each column and then filters the records based on their PRODUCT_NAME.
Hence from a Performance standpoint, HAVING is slower than WHERE Clause and should be avoided when necessary.
Now, as we looked at primary difference in practice. Let us look at key differences betwwen the two.
Difference between WHERE and HAVING Clause
WHERE Clause | HAVING Clause |
| 1. The WHERE Clause is used to filter records from a table based on a given condition. | 1.The HAVING Clause is used to filter records from groups rather than a table, based on a given condition. |
| 2. It cannot be used with Aggregate Functions such as SUM, COUNT, etc, and can implement only Single Row Functions such as LOWER, UPPER, etc. | 2. It can be used with Aggregate functions and implements in column operations. |
| 3. From a performance perspective The WHERE Clause is faster than the HAVING Clause. | 3. The HAVING Clause is slower due to aggregate calculations. |
| 4. It can be used without a GROUP BY Clause. If a query has GROUP BY then WHERE Clause should be placed before it. | 4. It can only be used with the GROUP BY Clause as it filters records from groups. Moreover, it comes after the GROUP BY Clause. |
| 5. It can be used with SELECT, UPDATE as well as DELETE commands i.e. works well with both DML and DQL commands. | 5. It can be used with only SELECT(DQL) statements. |
| 6. In simple words, it acts as a Pre-Filter. | 6. It acts as a Post-Filter. |
So, that’s all about difference between where and having clause in SQL. You can try the above explained queries with different use cases of both WHERE and HAVING Clauses and see them in detail.
