Monday, August 9, 2010

When GROUP BY is not used, HAVING behaves like a WHERE clause

If you have gone through BOL for HAVING keyword, you have seen above sentence at the top of the page. What does it exactly mean? Similar question raised up during my class too; Seems like this sentence makes all of us confused :).

It says that HAVING behaves like WHERE when GROUP BY is not used. Then where we can use HAVING without using GROUP BY? You just cannot replace WHERE with HAVING. But for some extent, HAVING can be used without GROUP BY. Think a about a scenario where you need to see whether number of orders placed by customers reach to certain level, let’s say 100, if so, get the number of orders. If there is a requirement like that, you can write a T-SQL like below;

USE AdventureWorks2008
FROM Sales.SalesOrderHeader
HAVING Count(*) > 100

Here we have used HAVING without GROUP BY. What has happened here is, the entire row-set is considered as a group, hence HAVING works fine. I did not come across any other places this can be used, please share with me if you have.

No comments: