Having Clause
HAVING clause is used to restrict the groups of rows defined through the GROUP BY clause. This is same to the WHERE clause that acts as a filter to the SELECT clause. The HAVING clause is used along with GORUP BY. The order of precedence in a SELECT list is listed as follows:
1. The rows that do not satisfy the condition are removed If a query contains a WHERE clause.
2. Oracle forms and calculates the groups as specified in the GROUP BY clause.
3. Oracle removes the records which do not satisfy the condition in the HAVING clause.
Let Consider a situation where all the clerks in every department who lie within the specified minimum and maximum salary must be displayed the maximum salary must not exceed 5000 and the minimum salary must not be less than 1000. The subsequent example performs this checking.
Example
SELECT deptno,MIN(sal),MAX(sal) FROM emp WHERE job=’CLERK’ GROUP BY deptno
HAVING MIN(sal) >=1000 AND MAX(sal) <=5000;
The output looks like:
DEPTNO MIN(SAL) MAX(SAL)
------- ------- --------
10 1300 1300
In this case, there is only one department which satisfies the both conditions.
The records are processed in this way:
First, the records where the job is not CLERK are eliminated. Another, the minimum and maximum salary of every department is determined and the HAVING condition is performed on the result of the rows selected through the GROUP BY clause.After that, let us display the total number of employees in each department is displayed designation wise. In addition, the number of employees in every department and for every job must exceed 2.
Example
SELECT job,deptno,COUNT(*) FROM emp GROUP BY job,deptno HAVING COUNT(*) >=2
ORDER BY deptno;
Given query selects the deptno, job and the total count from the employee table and grouping is done first on the job and then based on the deptno and the records are displayed where the count is greater than or equal to 2.
The output would look like:
JOB DEPTNO COUNT(*)
--------- --------- ---------
ANALYST 20 2
CLERK 20 2
SALESMAN 30 4