Difference between a "where" clause and a "having" clause?

Questions by Beena   answers by Beena

Editorial / Best Answer

Answered by: Ankush Sharma

  • Sep 14th, 2011

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.

for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.

The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

Using the code 

Code
  1. SELECT cusnum, lstnam, init
    2. 

  2. FROM Test
    3. 

  3. WHERE state IN ('CA', 'LA')
    4. 

  4.  
    5. 

  5. CUSNUM LSTNAM INIT BALDUE
    6. 

  6. ====== ============ ==== ========
    7. 

  7. 938472 John G K 37.00
    8. 

  8. 938485 Mark J A 3987.50
    9. 

  9. 593029 Lily E D 25.00
Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query. 
Code
  1. SELECT state,SUM(baldue)
    2. 

  2. FROM Test
    3. 

  3. GROUP BY state
    4. 

  4. ORDER BY state
    5. 

  5.  
    6. 

  6. State Sum(Baldue)
    7. 

  7. ===== ===========
    8. 

  8. CA 250.00
    9. 

  9. CO 58.75
    10. 

  10. GA 3987.50
    11. 

  11. MN 510.00
    12. 

  12. NY 589.50
    13. 

  13. TX 62.00
    14. 

  14. VT 439.00
    15. 

  15. WY .00
Using Having 
Code
  1. SELECT state,SUM(baldue)
    2. 

  2. FROM Test
    3. 

  3. GROUP BY state
    4. 

  4. HAVING SUM(baldue) > 250
    5. 

  5.  
    6. 

  6.  
    7. 

  7. State Sum(Baldue)
    8. 

  8. ===== ===========
    9. 

  9. GA 3987.50
    10. 

  10. MN 510.00
    11. 

  11. NY 589.50
    12. 

  12. VT 439.00

Showing Answers 1 - 26 of 26 Answers

rahultripathi

  • Sep 20th, 2005
 

Having can used only with the select statement .it is typically used with group by clause . when it is used without group by it work like where clause 

  Was this answer useful?  Yes

Ajay Sharma

  • Oct 17th, 2005
 

WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query

  Was this answer useful?  Yes

Ajay Sharma

  • Oct 17th, 2005
 

WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query

  Was this answer useful?  Yes

vini

  • Mar 13th, 2006
 

1. Having clause is usually used with Group By clause although it can be  used    without it too.

2. 'Having' is just an additional filter to 'Where' clause.

3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.

  Was this answer useful?  Yes

culver_lake

  • Mar 19th, 2006
 

Where and Having are two different things.  Having is NOT just another WHERE clause.  DB2 will not let you use HAVING without GROUP BY .  SYBASE and SQL Server allow HAVING to be used alone but the results can be very unexpected.  Be very careful of this, it usually will not produce what you want.

WHERE applies to rows, HAVING applies to summarized rows (summarized with GROUP BY) if you wanted to find the average salary in each department GREATER than 333 you would code:

SELECT  DEPARTMENT, AVG(SALARY)

FROM     EMP

WHERE DEPARTMENT > 333

GROUP BY DEPARTMENT

IF you then wanted to filter the intermediate result to contain departments where the average salary was greater that 50,000 you would code:

SELECT  DEPARTMENT, AVG(SALARY)

FROM     EMP

WHERE DEPARTMENT > 333

GROUP BY DEPARTMENT

HAVING AVG(SALARY) > 50000.

Where executes first

GROUP BY next

and finally HAVING

aruna

  • Apr 3rd, 2006
 

where clause can be used any type of sql statements

but having clause can be used with only  group functions.

  Was this answer useful?  Yes

Tushar

  • May 9th, 2007
 

You can have only group functions in having clause.

  Was this answer useful?  Yes

xsgao

  • May 9th, 2007
 

As stated in the SQL Server help, for Having clause:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

So you can use having WITHOUT group by clause.  This is a sample, although it might make no sense of business value:

select max(state) from States having count(State) > 60

  Was this answer useful?  Yes

SQLTweety

  • May 16th, 2007
 

For faster execution of SQL commands always use more conditions in WHERE clause than in HAVING clause.
First SQL server filters the rows using WHERE conditions and then performs group by on remaining rows and then filters the rows agian with HAVING.

  Was this answer useful?  Yes

priya

  • Jul 30th, 2007
 

Here both are filter conditions
where clause is used when from is used in the select condition..
having clause is used when group by function in the select condition....

  Was this answer useful?  Yes

hema

  • Aug 2nd, 2007
 

The basic difference between having and where clause is simply that you cannot use aggregate functions in where clause but in having you can specify
example- having avg(marks)
but try to recall you cannot use something like this-where avg(marks)

  Was this answer useful?  Yes

navin05_06

  • Jun 6th, 2008
 

Pls  check your answer practically first then only write it.
WE cant use HAVING clause without group functions IT will show error.So pls mr.Rahul Be Carefull next time

  Was this answer useful?  Yes

mayur.joshi

  • Nov 16th, 2009
 

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

  Was this answer useful?  Yes

Thirupathi

  • Aug 8th, 2011
 

Both Are Used Condition Purpose ,But Main Difference is Where Clause Is used Before the GROUP BY , HAVING Clause Is used After the GROUP BY

  Was this answer useful?  Yes

Ankush Sharma

  • Sep 13th, 2011
 

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.

for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.

  Was this answer useful?  Yes

Ankush Sharma

  • Sep 13th, 2011
 

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.


for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.


The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.


Using the code


Code
  1. SELECT cusnum, lstnam, init

    2. 

  2. FROM Test

    3. 

  3. WHERE state IN ('CA', 'LA')

    4. 

  4.  

    5. 

  5. CUSNUM LSTNAM INIT BALDUE

    6. 

  6. ====== ============ ==== ========

    7. 

  7. 938472 John G K 37.00

    8. 

  8. 938485 Mark J A 3987.50

    9. 

  9. 593029 Lily E D 25.00



Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.

Code
  1. SELECT state,SUM(baldue)

    2. 

  2. FROM Test

    3. 

  3. GROUP BY state

    4. 

  4. ORDER BY state

    5. 

  5.  

    6. 

  6. State Sum(Baldue)

    7. 

  7. ===== ===========

    8. 

  8. CA 250.00

    9. 

  9. CO 58.75

    10. 

  10. GA 3987.50

    11. 

  11. MN 510.00

    12. 

  12. NY 589.50

    13. 

  13. TX 62.00

    14. 

  14. VT 439.00

    15. 

  15. WY .00


Using Having 

Code
  1. SELECT state,SUM(baldue)

    2. 

  2. FROM Test

    3. 

  3. GROUP BY state

    4. 

  4. HAVING SUM(baldue) > 250

    5. 

  5.  

    6. 

  6.  

    7. 

  7. State Sum(Baldue)

    8. 

  8. ===== ===========

    9. 

  9. GA 3987.50

    10. 

  10. MN 510.00

    11. 

  11. NY 589.50

    12. 

  12. VT 439.00


Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions