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

Fortenberry

FYI, I have compiled the optimization techniques about having clause, In case anyone wishes to optimize the query. junaidtechblog.wordpress.com/2019/09/04/… – Lachrymatory

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

Sausa

under Sybase DB it has the same execution time for 150 rows :) – Fortenberry

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 ... – Sausa

... should give you the data on Sybase. Check this link for more information: groups.google.com/group/comp.databases.sybase/browse_thread/… – Sausa

FWIW, in MS SQL Server 2005, the execution plan is identical. – Glorygloryofthesnow

wouldn't it depend on the size of the table & subset and the cost of the where clause across that entire table vs the cost of the where clause against the subset? – Sheathe

This suffers from some fundamental misunderstandings. The SQL standard says what a statement returns in terms of its inputs, and nothing about performance. – Adama

@Adama Of course it says nothing about performance directly, but it does say what does the statement do: in this case "elimination of groups from the result of the previous clause" so it's unlikely to be faster in the general case. Of course specific implementations and optimizations under specific conditions can make it equally fast and that's why looking at the plan is the important part of the answer. – Sausa

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.

Zaragoza

Heh, "it depends on the engine but I believe they all behave like this" :-) – Sausa

Well, I can only speak of what I know and speculate on the rest :) – Zaragoza

Answer 3

The two queries are equivalent and your DBMS query optimizer should recognise this and 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 moved 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.

Supremacy

Answer 4

"WHERE" is faster than "HAVING"!

The more complex grouping of the query is - the slower "HAVING" will perform to compare because: "HAVING" "filter" will deal with larger amount of results and its also being additional "filter" loop

"HAVING" will also use more memory (RAM)

Altho when working with small data - the difference is minor and can absolutely be ignored

Crites

Answer 5

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

Joviality

Answer 6

Saying they would optimize is not really taking control and telling the computer what to do. I would agree that the use of having is not an alternative to a where clause. Having has a special usage of being applied to a group by where something like a sum() was used and you want to limit the result set to show only groups having a sum() > than 100 per se. Having works on groups, Where works on rows. They are apples and oranges. So really, they should not be compared as they are two very different animals.

Carbaugh

Answer 7

"Having" is slower if we compare with large amount of data because it works on group of records and "WHERE" works on number of rows..

"Where" restricts results before bringing all rows and 'Having" restricts results after bringing all the rows

Melosa

Answer 8

Both the statements will be having same performance as SQL Server is smart enough to parse both the same statements into a similar plan.

So, it does not matter if you use WHERE or HAVING in your query.

But, ideally you should use WHERE clause syntactically.

Kirkwall