Igor Makedon (1/28/2013)
Are the folowing two SELECT statements equvalent (i.e. -- see my original question, -- "return exactly the same row set" no matter "what data is")?
SELECT xx FROM yy WHERE xx >=5 GROUP BY xx
SELECT xx FROM yy GROUP BY xx HAVING xx >=5
NO, they are not equivalent, atlhough they may return the same result set.
Since xx is a non-aggregate column, "WHERE xx ..." is correct.
In some SQLs, SQL will not check xx with HAVING until after the GROUPing is done. SQL Server now effectively rewrites the second query to match the first. But it's wrong to rely on that. I would suspect the second query is not even guaranteed to be accepted by SQL itself as a valid query -- might depend on the SQL engine and/or version.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.