Which SQL statement is faster? (HAVING vs. WHERE...)

Question

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 

Answer 1

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.)

Answer 2

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.