difference between where and having clause in sql server

Introduction

Both Where and and having clause used to filter the record but for different purpose.

Lets Get Started

Where clause used to filter the data by row where as Having clause is used to filter based upon the aggregate data.

Lets Understand practically.

I created a table as TEST-INSERT and inserted some value to it as below.

CREATE TABLE [TEST-INSERT]

( [RNO] [int] NULL, [ID] [int] NULL, [NAME] [varchar](20) NULL, [Category] [varchar](50) NULL)

INSERT INTO TEST-INSERT VALUES(1,1,'Mark','Teacher')

INSERT INTO TEST-INSERT VALUES(2,2,'Ben','Teacher')

INSERT INTO TEST-INSERT VALUES(3,3,'Paul','Student')

INSERT INTO TEST-INSERT VALUES(4,4,'Struat','Student')

INSERT INTO TEST-INSERT VALUES(5,5,'RK','Cleark')

Table will display like below.

RNO ID NAME Category

----------- ----------- -------------------- --------------

1 1 Mark Teacher

2 2 Ben Teacher

3 3 Paul Student

4 4 Struat Student

5 5 RK Cleark

Difference-1

If we want to select some specific row then we can use where clause as shown below.

SELECT * FROM TEST-INSERT WHERE ID=2

If we want to count based up on the category then we can use Having clause.
See below query.

SELECT Category,COUNT(1) "Teacher Count" FROM TEST-INSERT GROUP BY Category HAVING Category='Teacher'

Difference-2

We can use where clause in Update query where as we can not use Having clause in Update query.

UPDATE TEST-INSERT SET Category='Student' WHERE ID=5

Difference-3

We we are using Having clause then Group by must be there in the query but in case of where no need any clause .

Difference-4

If we want to search any record from the table then we should go for Where clause.

If we are checking the duplicate record in a table or checking the count of a record based upon category we should go for Having clause.

Interview questions and Answers

1. Can we use Where and Having clause in a same table?

Yes,We can . See the below query.

SELECT Category FROM TEST-INSERT WHERE Category in('Student','Teacher') GROUP BY Category HAVING Category='TEACHER'

2. How to check the duplicate record from a table?

SELECT Category,COUNT(1) "Teacher Count" FROM TEST-INSERT GROUP BY Category HAVING COUNT(Category)>1

The above query will count the duplicate record whose count is more than 1.

2. Can we write Having Clause without Group By?

No,Not Possible.

Thanks.....................