Where Clause :- Used to filter the records from the table before group by cluse.
Having Clause :- Used to filter the grouped records after group By clause.
2 Users have rated as useful.
Login to rate this answer.
Sunil
Answered On : Mar 29th, 2006
To Restrict the group by clause ,Having is used .
1 User has rated as useful.
Login to rate this answer.
Rohan Deshpande
Answered On : Jan 29th, 2007
WHERE clause is used to restrict the rows.WHEREAS HAVING clause is used to restrict the groups.
1 User has rated as useful.
Login to rate this answer.
saranya
Answered On : 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!
Login to rate this answer.
anil kumar
Answered On : 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...
Login to rate this answer.
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"
Login to rate this answer.
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
1 User has rated as useful.
Login to rate this answer.
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.
Login to rate this answer.
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;
Login to rate this answer.
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.
Login to rate this answer.
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..
Login to rate this answer.
we have toapply the conditionBefore grouping the records then you have touse WHERE clause.
we have to apply the condition after grouping the records then you have to apply HAVING clause.
Thanks & Regards
K.Santhosh
Login to rate this answer.
