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