It has been some time since I have posted my last article let us kick-start with the next one…
Generally there is little confusion in using the Where and Having Clauses. Let us understand with the help of this article
Where clause specifies the filter condition for the table. It is optional to use the where clause in a SQL Query. Generally we use the where clause to simply restrict the number of rows returned. It gives us the set of records which are required for processing.
Create a table Customers by using the below mentioned script
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'CUSTOMERS' AND type = 'U') DROP TABLE CUSTOMERS CREATE TABLE CUSTOMERS ( CUSTID VARCHAR(15) NOT NULL PRIMARY KEY , CUSTNAME VARCHAR(20) , CITY VARCHAR(15) );
Insert some records into Customers using the below mentioned query
INSERT INTO CUSTOMERS VALUES ('C1','General Stores','Ooty'); INSERT INTO CUSTOMERS VALUES ('C2','Blue Star Bakery','Ooty'); INSERT INTO CUSTOMERS VALUES ('C3','Fortune Restaurent','Ooty'); INSERT INTO CUSTOMERS VALUES ('C4','Blue Hills Bakery','Conoor');
Get the customer details who are located in Ooty.
SELECT CUSTID, CUSTNAME, CITY FROM CUSTOMERS WHERE CITY = 'Ooty'
From the above result we can see that the customer who are located in Ooty is returned. It applies the filter on each row and the records satisfies the where condition are only returned by the Select statement.
Having clause specifies the filter condition for the aggregated data. Having clause is generally used with GROUP BY to filter the aggregated data. When it is used alone it more or less behaves like where clause.
It is not a row by row filter like Where clause and it applies the filter condition on the aggregated data, when it is used along with Group By.
Let us understand it with the help of an example
Create a table called order with the help of below script
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'ORDERS' AND type = 'U') DROP TABLE ORDERS CREATE TABLE ORDERS ( ORDERID INT NOT NULL PRIMARY KEY, ITEM VARCHAR (50), QUANTITY INT, ORDERSTATUS VARCHAR (15), CUSTID VARCHAR(15) REFERENCES CUSTOMERS (CUSTID) );
Insert some records into the Orders table using the below mentioned query
INSERT INTO ORDERS VALUES (1,'Pepsi',50,'A','C1'); INSERT INTO ORDERS VALUES (2,'Coke',50,'B','C1'); INSERT INTO ORDERS VALUES (3,'Mirinda',100,'B','C2'); INSERT INTO ORDERS VALUES (4,'Sprite',60,'','C2'); INSERT INTO ORDERS VALUES (5,'7up',35,'S','C2'); INSERT INTO ORDERS VALUES (8,'Fanta',80,'','C2');
SELECT ORDERID, ITEM, QUANTITY, ORDERSTATUS, CUSTID FROM ORDERS
We need to get the Customer for whom the total quantity is greater than 100.
Now we will design our script to get the desired result.
SELECT CUSTID, SUM(QUANTITY) AS TOTALQUANTITY FROM ORDERS GROUP BY CUSTID HAVING SUM(QUANTITY) > 100
Now from the above result we can see the customer with total quantity more than 100 is displayed.
We saw the functionality of the Where Clause and Having Clause. Let us see the difference between them and explain it with the help of a scenario. It shows that how we can use the Where and Having Clauses.
The scenario mentioned below better explains the difference between the where and the having clause.
We need to get the minimum Order Status for the customer without considering the NULL values for the ORDERSTATUS.
Using Where Clause:
SELECT C.CUSTNAME, MIN(O.ORDERSTATUS) MINORDERSTATUS FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTID = O.CUSTID WHERE O.ORDERSTATUS <> ' ' GROUP BY CUSTNAME
Let us check the same with the help of the Having clause.
SELECT C.CUSTNAME, MIN(O.ORDERSTATUS) MINORDERSTATUS FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTID = O.CUSTID GROUP BY CUSTNAME HAVING MIN(O.ORDERSTATUS) <> ' '
In the above example the data is grouped first based on the group by clause and then the filter is applied to the groups. Since it grouped the data first and then filtered, It filtered the customer Blue Star Bakery because the minimum value will be empty. It has removed the entire group from the selection.
Using Having Clause for this requirement is not suitable as it filters the entire group. Based on the requirement we need to choose between the Where and Having Clause.
Hope that it would have given you fair bit of understanding about the Where and Having Clause and then the difference between them.
Following are the few theoretical differences.
1) Where clause applies the filter on the individual rows. Having clause applies the filter on the aggregated data.
2) Where clause does not contain aggregate function, Having clause contains aggregate functions.