Using Where and Having Clauses

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:

       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.

 Let us understand it with the help of an example

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.

Query:

SELECT CUSTID, CUSTNAME, CITY
FROM CUSTOMERS
WHERE CITY = 'Ooty'

Result:

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:

            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

Query:

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');
Let us see the data in the table Orders

Query: 

SELECT ORDERID, ITEM, QUANTITY,
ORDERSTATUS, CUSTID
FROM ORDERS
Result:
 

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.

Query:

SELECT CUSTID, SUM(QUANTITY) AS TOTALQUANTITY
FROM ORDERS
GROUP BY CUSTID
HAVING SUM(QUANTITY) > 100 

Result:

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.

Scenario:

We need to get the minimum Order Status for the customer without considering the NULL values for the ORDERSTATUS.

Using Where Clause:
Query:

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
Result:
 
 
 
In the above example the query engine have removed the null values from the table using the Where Clause. Then it identified the minimum of order status for the customer. It has removed the two null values for the Customer Blue Star Bakery and then identified the minimum order status.
 
Using Having Clause:

Let us check the same with the help of the Having clause.

Query:

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) <> ' '

Result:

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.

Disclaimer :- The examples that has been shown here are just for the demo purpose there might be better ways of writing the same query.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s