WHERE and HAVING clause in Microsoft SQL-Server

WHERE and HAVING clause in Microsoft SQL-Server

People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.

“WHERE” is a condition used with T-SQL statement. It always apply to the row and “HAVING” is a clause which always apply to the summarized row (should use with group by).

“WHERE” could be used to make condition on SELECT statement or else can be used with single row function as well. You must have to specify “WHERE” condition before GROUP BY clause.

Whereas, “HAVING” clause is used to make condition on “GROUP BY” data and suppose to be used after “GROUP BY” clause.

Moreover, “HAVING” clause can be used in SELECT statement with “GROUP BY” clause and whenever “GROUP BY’ is not present, “HAVING” will work as “WHERE”.

That’s enough theory, right???? J Anyway, let us see this practically with creating one demo table and query it along with WHERE and HAVING clause.

--Create one table

CREATE TABLE BlogCount

(

BloggerName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT

)

--Insert records in above table

INSERT INTO BlogCount VALUES('Ritesh','SQL',2005,10)

INSERT INTO BlogCount VALUES('Ritesh','SQL',2006,17)

INSERT INTO BlogCount VALUES('Ritesh','SQL',2007,124)

INSERT INTO BlogCount VALUES('Ritesh','SQL',2008,124)

INSERT INTO BlogCount VALUES('Ritesh','.NET',2008,24)

INSERT INTO BlogCount VALUES('Alka','SQL',2007,14)

INSERT INTO BlogCount VALUES('Alka','.NET',2007,18)

INSERT INTO BlogCount VALUES('Alka','SQL',2008,14)

--Query with WHERE and HAVING clause together

SELECT BloggerName,AVG(total) AS 'Average' FROM blogcount

WHERE Topic='SQL'

GROUP BY BloggerName

HAVING AVG(total)>25

First WHERE clause filter the record set and then it will be passed to GROUP BY and HAVING for further filtering.

Reference: Ritesh Shah