When do you use WHERE clause and when do you use HAVING clause?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
  • Interview Candidate
  • 13
  • 10067

Showing Answers 1 - 13 of 13 Answers

Sunil

  • Mar 29th, 2006
 

To Restrict the group by clause ,Having is used .

Rohan Deshpande

  • Jan 29th, 2007
 

WHERE clause is used to restrict the rows.WHEREAS HAVING clause is used to restrict the groups.

saranya

  • Jun 19th, 2007
 

When we have to group a number of records in the table using a specific field name say in the database of a bank we would wish to group the records based on their branch name we would use having clause.
When selecting i.e viewing the records in order to fetch tuples subjected to certain conditions where clause is used.

Thanx!

  Was this answer useful?  Yes

anil kumar

  • Aug 2nd, 2007
 

The WHERE clause is used to depend on the table while checking the condition..
                                    AND                                                 
The HAVING clause is used where the data is having in the perticular table...while checking the condition...

  Was this answer useful?  Yes

LRANI

  • Aug 3rd, 2007
 

WHERE and HAVING clause both restricts the data but WHERE clause used on the columns/expression whereas HAVING clause can be used on group of data.
For example : to find out employees who are working in deptno 30 from emp table:
SQL statement :
 SELECT * FROM  emp WHERE deptno=30;
will return the data.
SELECT * FROM emp HAVING deptno=30;
will give an error: "Not a GROUP BY expression"

  Was this answer useful?  Yes

deviji2000

  • Nov 12th, 2007
 

When you want to mention any condition in the SQL statement we use WHERE Clause but you will get an error when you have group functions in your SQL statement and trying to include group functions in your WHERE clause.

To avoid the error you have to mention the group condition in HAVING clause.

example:

SELECT emp_no, emp_name, sal
FROM emp
WHERE sal >10000;

but you cannot use WHERE in

SELECT min(sal), deptno
FROM emp
WHERE min(sal) >10000
GROUP BY deptno;

the above statement raises error so replace 'WHERE' by 'HAVING' like

SELECT min(sal), deptno
FROM emp
GROUP BY deptno
HAVING min(sal)>10000;
this will work out.

bye!
viji

patilpravin_1981

  • Jan 29th, 2008
 

when query is based on condition we use where clause & there is no any aggregate function
when query is base on contion & aggregate function has to be use then we can use
having clause.

  Was this answer useful?  Yes

narmadac

  • Feb 11th, 2010
 

Avoid using HAVING clause in SELECT statement as it filters selected rows only after all rows have been fetched.  Instead use WHERE clause to reduce overheads.

select empno, ename from emp group by deptno having deptno=10;

In the above case first grouping is done and then restricted rows are fetched.  where as in the below case using the where clause fetches rows restricted by deptno.

select empno, ename from emp where deptno =10;

  Was this answer useful?  Yes

ankitagujrati28

  • May 3rd, 2010
 

when we want to give condition on taking group function result then we have to use HAVING CLAUSE after writing group by clause. And when we want to give simple condition then we can use WHERE CLAUSE after FROM CLAUSE and before GROUP BY CLAUSE.

  Was this answer useful?  Yes

anupsunder92

  • Jun 10th, 2010
 

The WHERE clause is a conditional construct used to restrict or limit rows based on some condition.

If the condition is satisfied then the filtered rows are returned as output.  Consider this example

SELECT ename,job_id
FROM emp
WHERE deptno=10
ORDER BY ename desc;

The above example returns the employee name and job id of employees who are working in department number 10.

The rows are filtered here based on the condition mentioned in the WHERE clause
HAVING clause is also a conditional clause similar to WHERE
The HAVING clause was introduced by oracle coz you can't use group functions like
SUM,MIN,MAX,COUNT,AVG,STDDEV,VARIANCE  in the WHERE clause

Consider this example 

SELECT ename,avg(salary)
FROM emp
WHERE avg(sal)>1000

This example gives you an error stating "group function is not allowed here", which means one can't use group functions in WHERE clause.  We can rewrite the above example as

SELECT ename,avg(sal)
FROM emp
HAVING avg(sal)>1000
GROUP BY ename;

This displays the employee name and average salary of employees whose average salary is more than 1000

NOTE: Group function must always be used when you are using having clause         
Because you have used a group function in the having clause to filter the records..

  Was this answer useful?  Yes

Santhoshkandula

  • May 11th, 2011
 



   we have to apply the condition Before grouping the records then you have to use WHERE clause.
   we have to apply the condition after grouping the records then you have to apply HAVING clause.


  Thanks & Regards

    K.Santhosh

  Was this answer useful?  Yes

Mushtaque

  • Aug 25th, 2013
 

WHERE clause can contains condition that must be met and should directly follow the from clause. And HAVING clause can precede GROUP BY clause,but it is more logical to declared in after GROUP BY clause.

1.WHERE clause.....
SELECT Empno,Ename,sal FROM Emp WHERE JOb<>1500;

2.HAVING clause.....
SELECT Deptno, Job, SUM(Sal),AVG(Sal), FROM Emp GROUP BY Deptno,Job HAVING AVG(Sal)>2850;

  Was this answer useful?  Yes

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