Is the HAVING clause redundant?

Question

The following two queries yield the exact same result:

select country, count(organization) as N
from ismember
group by country
having N > 50;

select * from (
  select country, count(organization) as N
  from ismember
  group by country) x
where N > 50;

Can every HAVING clause be replaced by a sub-query and a WHERE clause like this? Or are there situations where a HAVING clause is absolutely necessary/more powerful/more efficient/whatever?

Malanie

You should define RDBMS to the question I guess. Your first query is not valid in SQL Server 2008, because you can't make a reference in the Having on an alias in the select. Only in the OrderBy part, because of the Logical query processing. – Sinclair

I suspect MySQL? The first query is not valid in Oracle either for the same reason. – Ultimogeniture

@Ultimogeniture Yes, I just added the MySQL tag to clarify. Thanks. – Malanie

See HAVING A Blunderful Time or Wish You Were WHERE – Salmanazar

Answer 1

There are 2 questions asked here: The answer to the first of which is yes: The resultset of a HAVING-laden query is identical to the resultset of the same query executed as a subquery, decorated with a WHERE clause.

The second question is about performance and expressivity - here we go heavily into implementation. On MySQL there is a thin red line, where the performance starts to drift apart: The moment the resultset of the inner query can no longer be held in memory. In this case, MySQL will create an on-disk representation of the inner query, then use the WHERE selector on it. This will not happen, if the HAVING clause is used, the disqualified group will be dropped from the result set.

This implies, that the higher the selectivity of the HAVING clause, the more performance relevance it has: Consider result set of a million rows of the inner query, that is reduce by the HAVING clause to 5 rows - it is very likely, that the result set of the inner query wouldn't be held in memory, but it is very likely, that the final result set would.

Edit

I had this once: The query selected the few outliers from a very evenly distributed table (Number of pieces produced on a physical machine in a workshop per day). I investigated because of the high IO-load.

Edit 2

Please keep in mind, that the query cache is not used for subqueries - IMHO a place development should focus more on - so the subquery pattern will not profit from the inner query being a cached result set.

Coppersmith

Answer 2

In Sql Server 2008 two similar queries have exactly the same execution plan:

I've also studied a lot of queries generated by Entity Framework (with SS 2008) and so far I never saw a query with a HAVING clause. Grouping queries with a condition on an aggregated result are always translated into a query with a sub query. I trust the ADO.Net team knows with they're doing...

Darrendarrey

I wouldn't trust that at all. EF (and Linq-to-SQL) produce notoriously bad queries. – Mythify

@RobFarley I know that they can't compete with manually crafted and optimized queries but for automated queries they're not that bad. You should know some do's and dont's when writing linq, though. – Darrendarrey

maybe the are similar as Sql Server converted the subquery version to the aggregate version of the query? ツ – Alcatraz

Answer 3

The HAVING clause is very useful to avoid the added complexity of sub-queries. However, the two are logically equivalent and every HAVING clause can be rewritten using a sub-query as you have.

In case you're curious, you could also write every WHERE clause as a HAVING clause if you're prepared to take GROUP BY to the extreme.

Mythify

Not sure your last line is true is it? Suppose a table with one column called number and three rows VALUES (1),(1),(2) How can you simulate SELECT number FROM T WHERE number = 1 with HAVING? – Salmanazar

That would only return one row. – Salmanazar

Oh, sorry - misread the list of numbers (went over two lines on my mobile). You could introduce a differentiator like a row_number, and include that in the group expression. If you group by something that's unique, HAVING and WHERE become equivalent. – Mythify

Answer 4

I know, that you changed it from general to MySQL, but I would like to add here a (may usefull) note. With a little modification I tried your query in SQL Server 2008.

Just for anybody who wants more detail in it, the executionplan of the two query is even exactly the same in SQL Server 2008. So the optimalizer processing the two command on the same way with the same performance and estimations.

Consols

Answer 5

IMHO, using the HAVING clause should be efficient because there would an additional pass on the worktable that contains the grouped results on top of which the filtering criteria is run, in the second case.

Felice

Sub-queries don't get expanded into worktables. The two queries (albeit with the alias problem removed for other platforms) should be treated identically. – Mythify

@RobFarley This is not entirely true: If the result set exceeds a certain size, it will be materialized. – Coppersmith

Ok. Not in SQL Server or Oracle. Those systems will simplify the query out. – Mythify

I was specifically talking MySQL - should have made this clearer. Sorry for that. – Coppersmith

Answer 6

Logically yes the result will be the same on the end. But performance might differ. The HAVING clause might lead the DB to change a different execution plan.

A note to the guys above (can't directly comment somehow) - the execution plan does not only depend on your query. It might also get adjusted by the DB depending on statistics, like table size etc on runtime. That said for DB2 at least...

Depress