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

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

Answers


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


Need Your Help

Removing the description box when creating taxonomies

php drupal drupal-7 taxonomy drupal-taxonomy

When creating taxonomy terms there is a custom descriptiom box called 'Term description textarea'. Is there a way to remove it?

How are virtual functions and vtable implemented?

c++ polymorphism virtual-functions vtable

We all know what virtual functions are in C++, but how are they implemented at a deep level?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.