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.
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.
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...
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"
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.
RE: When do you use WHERE clause and when do you use HAVING clause?
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.
2 
0