views:

2352

answers:

5
SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
    FROM  PRACOWNICY
    GROUP BY NR_DZIALU
    HAVING NR_DZIALU = 30

or

SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
    FROM PRACOWNICY
    WHERE NR_DZIALU = 30
    GROUP BY NR_DZIALU
A: 

I'd expect the WHERE clause would be faster, but it's possible they'd optimize to exactly the same.

ysth
+12  A: 

The theory (by theory I mean SQL Standard) says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster. On SQL Standard compliant DBMSs in this regard, only use HAVING where you cannot put the condition on a WHERE (like computed columns in some RDBMSs.)

You can just see the execution plan for both and check for yourself, nothing will beat that (measurement for your specific query in your specific environment with your data.)

Vinko Vrsalovic
under Sybase DB it has the same execution time for 150 rows :)
Murzyn1
I said execution plan, where you can see what steps the database will perform to get your data. 150 rows are too few to notice any difference in execution time, but if the plan's different, then that will matter on tables with a greater amount of rows. "set showplan on" before running the query ...
Vinko Vrsalovic
... should give you the data on Sybase. Check this link for more information: http://groups.google.com/group/comp.databases.sybase/browse_thread/thread/abd5e99d77befbfa?pli=1
Vinko Vrsalovic
FWIW, in MS SQL Server 2005, the execution plan is identical.
Sören Kuklau
A: 

The only way to find that out is to benchmark them.

Justice
I didn't downvote you - but the suggestion that benchmarking is the "only" way to find out is at best over-simplistic. You'd have to explain why that's the case, given the theoretical analysis by Vinko, amongst other things.
Jonathan Leffler
+2  A: 

It might depend on the engine. MySQL for example, applies HAVING almost last in the chain, meaning there is almost no room for optimization. From the manual:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

I believe this behavior is the same in most SQL database engines, but I can't guarantee it.

Eran Galperin
Heh, "it depends on the engine but I believe they all behave like this" :-)
Vinko Vrsalovic
Well, I can only speak of what I know and speculate on the rest :)
Eran Galperin
+3  A: 

The two queries are equivalent and your DBMS query optimiser should recognise this and produce produce the same query plan. It may not, but the situation is fairly simple to recognise, so I'd expect any modern system - even Sybase - to deal with it.

HAVING clauses should be used to apply conditions on group functions, otherwise they can be mvoed into the WHERE condition. For example. if you wanted to restrict your query to groups that have COUNT(DZIALU) > 10, say, you would need to put the condition into a HAVING because it acts on the groups, not the individual rows.

Mike Woodhouse