HAVING vs. WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

HAVING vs. WHERE

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-22 : 14:36:33

When grouping data, I am unclear as to why one would place selection criteria in HAVING as opposed to WHERE. It seems like the same data is returned. Is there a performance advantage with HAVING?

I know enough to know that I don't know enough.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-22 : 14:38:50

I think WHERE filters the data before it's grouped (so you get less data to group) and HAVING allows you to filter the grouped data.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-22 : 15:37:02

select ID, sum(amount)
from table
where amount > 10
group by ID

The above says "only include rows from the table in which the amount column is > 10." Then, for each ID, it add ups the Amount column from the resulting rows and returns the totals.

select ID, sum(amount)
from table
group by ID
having sum(amount) > 10

That one says "take all the rows in the table(since there is no WHERE clause) and add up the AMount column for each ID. From the summarized result, only return rows in which the total amount is > 10."

Do you see the difference? When in doubt, create a small table. add some data. experiment ! see for yourself what you can do and what is returned. Don't try to learn this stuff on production data for the first time, practice on small sets of data that you can easily test and verify what is returned is what you expect.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 15:55:08

The where clause is for filtering rows to be included
The having clause is for aggreagate tests on the group.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources