SQL Server - what is difference between where clause and having clause ?

Asked By Amit Choudhary on 29-Jun-07 11:06 AM

end of post

Have you tried Books Online? - Peter Bromberg replied to Amit Choudhary on 29-Jun-07 03:39 PM

It installs with the product.Or, you can do it online:

http://msdn2.microsoft.com/en-us/library/aa274783(SQL.80).aspx

difference between where clause and having clause - Ahsan Murshed replied to Amit Choudhary on 21-May-08 05:24 AM

Use HAVING instead of WHERE when you want to establish a condition that involves a grouping (aggregating) function.
The difference is when the clause is applied. WHERE is applied much earlier in the creation of the result set and I believe can take advantage of indexes and other optimizations. HAVING is applied much later.

HAVING is usually much slower than using WHERE as the WHERE clause usually is more restrictive earlier on. However, HAVING is very useful when wanting to filter on aggregated or aliased columns as these are created after the application of the WHERE clause.

In other words,
HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
And,A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Referece:BOL,online forum:http://saloon.javaranch.com,blog:http://blog.sqlauthority.com

Difference between where clause and Having clause - Shailendrasinh Parmar replied to Amit Choudhary on 31-May-08 03:17 AM

The difference is that WHERE operates on individual rows, while HAVING operates on groups.

Typically you will have at least one column to GROUP BY, such as:

select cust_id
, count(distinct order_id) as orders
from sales
where order_date >= ‘2005-01-01′
group
by cust_id
having sum(order_amt) > 10000
This query returns the cust_id and number of orders for every customer who has order totals of $10,000 or
more (the HAVING condition) for orders made this year (the WHERE condition).

Also see the following article ::

http://www.devx.com/DevX/Tip/21295

eliza replied to Amit Choudhary on 22-Apr-10 06:34 AM

While working on a query to count some rows based upon a condition I found an interesting thing.If you were to use the COUNT function in a query for a condition then we have to use the HAVING http://www.mindfiresolutions.com/using-having-clause-with-count-function-in-sql-533.php instead of WHERE clause.

So the following query would return error:

$wrong_query = "SELECT count( `fk_mp` ) FROM wrong_tab GROUPBY `pg_name` WHERE count( `fk_mp` ) > 1";

jatin replied to eliza on 21-Apr-11 02:37 AM

this is very good explanation, for easy and better explanation you can visit
this link
http://dotnetpeoples.blogspot.com/2011/04/having-clause-vs-where-clause.html

jatin replied to Amit Choudhary on 21-Apr-11 02:37 AM

this is very good explanation, for easy and better explanation you can visit
this link
http://dotnetpeoples.blogspot.com/2011/04/having-clause-vs-where-clause.html