4 ms·
But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like.
by larsrc 4y ago
But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like.
SELECT year, COUNT(*) sales, SUM(price) income
FROM sales
HAVING sales > 10
AND income < 1000;
- p4bl0 4y agoWhy wouldn't SELECT * FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income FROM sales) WHERE nb_sales > 10 AND income < 1000; work just like your example?
- dspillett 4y agoFor more complicated examples, HAVING can produce easier to read/understand/maintain statements. There may also be performance differences depending on the rest of the query, but for simple examples like this exactly the same plan will be used, so the performance will be identical. Unfortunately, the simplest examples do not always illustrate the potential benefits of less common syntax.
- p4bl0 4y agoI totally agree with that, but I was responding to this statement: > Subselects can't do something like: (…) which is wrong.
- Linosaurus 4y agoThis should work, right? Just a bit more unnecessary text. SELECT year, sales, income FROM ( SELECT year, COUNT(*) sales, SUM(price) income FROM sales ) AS innerquery WHERE sales > 10 AND income < 1000;