Example: Correlated subquery in a HAVING clause

 

Suppose that you want a list of all the departments whose average salaries are higher than the average salaries of their areas (all departments whose WORKDEPT begins with the same letter belong to the same area). To get this information, SQL must search the CORPDATA.EMPLOYEE table.

For each department in the table, SQL compares the department's average salary to the average salary of the area. In the subquery, SQL calculates the average salary for the area of the department in the current group. For example:

     SELECT WORKDEPT, DECIMAL(AVG(SALARY),8,2)
      FROM CORPDATA.EMPLOYEE X       GROUP BY WORKDEPT       HAVING AVG(SALARY) >
         (SELECT AVG(SALARY)
            FROM CORPDATA.EMPLOYEE             WHERE SUBSTR(X.WORKDEPT,1,1) = SUBSTR(WORKDEPT,1,1))

Consider what happens when the subquery is executed for a given department of CORPDATA.EMPLOYEE. Before it is executed, the occurrence of X.WORKDEPT is replaced with the value of the WORKDEPT column for that group. Suppose, for example, that the first group selected has A00 for the value of WORKDEPT. The subquery executed for this group is:

  (SELECT AVG(SALARY)
     FROM CORPDATA.EMPLOYEE      WHERE SUBSTR('A00',1,1) = SUBSTR(WORKDEPT,1,1))

Thus, for the group considered, the subquery produces the average salary for the area. This value is then compared in the outer statement to the average salary for department 'A00'. For some other group for which WORKDEPT is 'B01', the subquery results in the average salary for the area where department B01 belongs.

The result table produced by the query has the following values.

WORKDEPT	AVG SALARY
D21	25668.57
E01	40175.00
E21	24086.66

 

Parent topic:

Correlated subqueries