6

I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that specifically addresses the issue.

I know the point of the HAVING statement is to introduce conditions on aggregations, just like WHERE introduces conditions on individual rows. However, what I'm seeing in this code is HAVING being used in lieu of WHERE on queries with aggregations. The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

For example:

SELECT id, filedate, SUM(amount)
FROM Sales
GROUP BY id, filedate
HAVING id = 123 AND filedate = '1/1/2018'

As opposed to:

SELECT id, filedate, SUM(amount)
FROM Sales
WHERE id = 123 AND filedate = '1/1/2018'
GROUP BY id, filedate

Are there performance implications or other advantages/disadvantages to this strategy?

I haven't tried running diagnostics myself, not a priority and I'd have to do it on my own time. However, I think I may if there isn't a clear answer on this.

My concern is how the optimizer views this query. Does it aggregate all data and then restrict the result set based on the HAVING clause, or does it realize it can apply the having conditions on the individual rows since they are specifically referencing non-aggregated columns?

EDIT: For my example queries and the actual SQL I'm rewriting, the plans are identical, but the queries are of similar complexity and I'm not yet knowledgeable enough to draw conclusions from the identical plans.

  • 1
    What does the query plan show you? – Erik Darling Jan 24 '18 at 19:47
  • For my particular example, it is the same, but I'm not sure I know enough to make conclusions on that, e.g. is the example too simple, will that always be the case. Will stats affect, etc. – Jacob Barnes Jan 24 '18 at 19:51
  • I did check out the query plans for the actual statements, but they are of similar complexity and left me with the same questions. – Jacob Barnes Jan 24 '18 at 19:56
13

The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

The problem here is in how you are describing what the HAVING clause applies to. The HAVING clause always applies to aggregated fields, which is all remaining columns post-aggregation. You are trying to show / say that the HAVING clause is not being applied to any aggregate functions, which is what they usually apply to. But in reality, the HAVING clause governs the result of that aggregate function, or, in your first example, the result of the grouping column. But in both cases, the aggregation has already been performed.

So in terms of performance (not to mention readability for others trying to update this code later), you use the WHERE clause to filter down to what will be aggregated, and then the HAVING clause to filter out what has been aggregated. And, while the result of a simple test as shown in the question masks the difference between the timing of the two (or logical placement in the sequence that the query is processed in) such that they "appear" to be doing the same thing, I would be quite surprised if it was not less efficient to aggregate a bunch of rows only to throw them out later when logically they could have been eliminated prior to storing / computing the aggregations. HOWEVER, if you do see that they execution plans are similar for this simple example, I am willing to bet that it is merely due to the optimizer seeing that it would be more efficient to make those HAVING conditions actual WHERE conditions as it rewrites the query before it executes it. But in that case, I still would advise against writing queries this way because you are making the optimizer take extra time to rewrite bad code when it should be spending that time / CPU cycles finding a more efficient plan. @DavidSpillett added (in a comment on this answer): "Furthermore, you are relying on the query planner seeing the optimisation potential, which it may not in more complex queries or if your code ends up ported to another database (or even just an older version of SQL Server)".

For what it's worth, even the Microsoft documentation for the HAVING clause stated that it acted as a WHERE clause when no GROUP BY was present. Now that the documentation is on GitHub, I was able to correct it recently via Pull Request #235: Correct and improve HAVING clause.

8

Solomon gives very good explanations, but to me, the easy answer is to remember the SQL query logical processing order as Itzik Ben-Gan wrote here The sequence is always

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So you see, if we can have a WHERE filter applied before GROUP BY, we may reduce the amount of data to be processed by GROUP BY, esp, WHERE operation can be extremely efficient when proper indexes exist. As such, I'd say if using WHERE and HAVING return the same result from business perspective, WHERE is always a winner over HAVING.

  • Shouldn't the ORDER BY be before the SELECT? After all, we can select columns (or, in the case of GROUP BY, aggregates) that are not included in the SELECT. – IanF1 Jan 25 '18 at 6:48
  • 1
    No, SELECT is before ORDER BY, the proof is that you can use a column alias, which is defined in SELECT clause, in the ORDER BY clause, but you cannot use an alias in any operator before SELECT, such as WHERE or GROUP BY. I do not understand what you mean by "we can select columns that are not included in the SELECT". – jyao Jan 25 '18 at 17:25
  • Apologies for the typo, I meant to say that we can Order By columns that are not included in the Select - such as SELECT FileName FROM Files ORDER BY FileId – IanF1 Jan 25 '18 at 19:50
  • Or in the case of an aggregate: SELECT FileId FROM FileDownloads GROUP BY FileId ORDER BY COUNT(Downloadid) – IanF1 Jan 25 '18 at 19:52
  • 1
    @IanF1, what you said has nothing to do with SELECT / ORDER BY sequence because for example you can use SELECT col_A from table where col_B > 0, here you use some column in WHERE clause which is not in SELECT list, but this is not a proof that WHERE operator SHOULD be before SELECT. :-) – jyao Jan 25 '18 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.