having clause and where clause

for the following SQL statement
select ...
group by col1
having col1 is not null
Does it always return the same result as
select ...
where col1 is not null
group by col1
?
The "where" one should have better performance, is it always true?Having gets evaluated after all the results have been returned and
aggregates calculated.
Where gets evaluated as the results are returned. At which point the filter
happens depends on the execution plan, but it will always be prior to
aggregation.
As a rule, only criteria on aggregate columns (sum, count, etc.) should be
in the having clause. Everything else belongs in the where.
Where SHOULD always be more efficient than having. HOWEVER, it is possible
(but highly unlikely) that the difference could cause SQL Server to use a
different execution plan that by some freak coincidence would perform better
with the having than the where. This would be the exception, and I would be
rather surprised if anyone could come up with such a case.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||They should return the same result. However, the version with the where
clause is (IMHO) better code. Where should be used when you want to compare
on the values in each row, use haveing when you want to compare on
aggregated values. So the SQL to retrive all customers with orders totaling
more than $1000 from New York would be something like:
Select ...
Where State = 'NY'
Group By CustomerID
Having Amount > 1000
Tom
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||Use having ONLY if you cannot achieve the functionality through the where
clause.
In case of having the result set is already generated and the filter is
applied.
And in this particular case that you have mentioned there won't be any
difference in the result (or so I think).