locked
Difference between where and having clause RRS feed

  • Question

  • User1281381861 posted

    Hi,All

    I found this Discussion From various Articles

    I want to share With all

     

    Here is The Difference

    Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:

    1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.

    2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.

    3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

     

     

    The HAVING clause allows you to filter the results of aggregate functions,

    such as COUNT() or AVG() or SUM(), or MAX() or MIN(), just to name a few.

    HAVING provides you a means to filter these results in the same query,

    as opposed to saving the results of a WHERE clause SQL statement to a temporary table

    and running another query on the temporary table results to extract the same results.

     

    Follow The Below Example This Will Clear Up

    Go

     

     

    Create table Test_Where_Having

    (id int identity,[Name] varchar(20),[Age] int,Amount int)

    Go

    insert into Test_Where_Having

    select 'Abc',20,100

    union all

    select 'Def',30,100

    union all

    select 'Ghi',52,500

    union all

    select 'Jkl',30,80

    union all

    select 'Mno',40,600

    union all

    select 'Pqr',60,500

    union all

    select 'Pqr',60,500

    union all

    select 'Abc',20,500

    Go

    --They both are use to exclude rows from the resultset,

    --but "where" is to filter the original set

    --and "having" is in case you are grouping.

    select [Name],[Age] from Test_Where_Having

    where Age>30

    --Output

    Name Age

    -------------------- -----------

    Ghi 52

    Mno 40

    Pqr 60

    Pqr 60

     

     

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    group by [Name],[Age]

    having Sum(Amount)>400

    --Output

    Name Age Total

    -------------------- ----------- -----------

    Abc 20 600

    Mno 40 600

    Ghi 52 500

    Pqr 60 1000

     

     

    --For Below Case These Queries Are Same As per Performance (Cost Of Query) And result basis

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    where Age>30

    group by [Name],[Age]

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    group by [Name],[Age]

    having Age>30

     

     

     

    --For Below Case These Queries Are Same As per Performance (Cost Of Query) And result basis

    select [Name],[Age] from Test_Where_Having

    where Age>30

    group by [Name],[Age]

    select [Name],[Age] from Test_Where_Having

    group by [Name],[Age]

    having Age>30

     

     

     

     

     

     

    --WHERE is used to filter rows. HAVING is usually used to filter rows after performing an aggregation.

    --Below Query is incorrect While using aggregate with where clause

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    where Sum(Amount)>30

    group by [Name],[Age]

    --U can use the above with Having

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    group by [Name],[Age]

    having Sum(Amount)>30

    --Output

    Name Age Total

    -------------------- ----------- -----------

    Abc 20 600

    Def 30 100

    Jkl 30 80

    Mno 40 600

    Ghi 52 500

    Pqr 60 1000

     

     

     

     

     

    --You can't use HAVING unless you also use GROUP BY.

    --Ie below Query is incorrect

    select [Name],[Age],Sum(Amount) As Total from Test_Where_Having

    having Sum(Amount)>30

     

     

     

    --One limitation when you use the HAVING clause as compare to WHERE clause.

    -- Having clause only supports the Grouped Columns & Aggregation filter..

    --Where there is a column level filter then always use the Where clause,

    --Use Having clause only for Aggregation filter.

    --For Example Below U cant use Amount column in Having Clause because it is not in grouped columns

    select [Name],[Age] from Test_Where_Having

    group by [Name],[Age]

    having Amount>30

     

    --For The Above Case U can Use Where Clause

    select [Name],[Age] from Test_Where_Having

    where Amount>30

    group by [Name],[Age]

     

     

     

     

    Tuesday, December 2, 2008 2:14 AM

All replies

  • User-687703514 posted
    Thanks budy, Its good.
    Tuesday, December 2, 2008 4:42 AM