Example: Correlated subquery in a WHERE clause

 

Suppose that you want a list of all the employees whose education levels are higher than the average education levels in their respective departments. To get this information, SQL must search the CORPDATA.EMPLOYEE table.

For each employee in the table, SQL needs to compare the employee's education level to the average education level for the employee's department. In the subquery, you tell SQL to calculate the average education level for the department number in the current row. For example:

  SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL    FROM CORPDATA.EMPLOYEE X    WHERE EDLEVEL >
      (SELECT AVG(EDLEVEL)
         FROM CORPDATA.EMPLOYEE          WHERE WORKDEPT = X.WORKDEPT)

A correlated subquery looks like an uncorrelated one, except for the presence of one or more correlated references. In the example, the single correlated reference is the occurrence of X.WORKDEPT in the subselect's FROM clause. Here, the qualifier X is the correlation name defined in the FROM clause of the outer SELECT statement. In that clause, X is introduced as the correlation name of the table CORPDATA.EMPLOYEE.

Now, consider what happens when the subquery is executed for a given row of CORPDATA.EMPLOYEE. Before it is executed, the occurrence of X.WORKDEPT is replaced with the value of the WORKDEPT column for that row. Suppose, for example, that the row is for CHRISTINE I HAAS. Her work department is A00, which is the value of WORKDEPT for this row. The subquery executed for this row is:

  (SELECT AVG(EDLEVEL)
     FROM CORPDATA.EMPLOYEE      WHERE WORKDEPT = 'A00')

Thus, for the row considered, the subquery produces the average education level of Christine's department. This is then compared in the outer statement to Christine's own education level. For some other row for which WORKDEPT has a different value, that value appears in the subquery in place of A00. For example, for the row for MICHAEL L THOMPSON, this value is B01, and the subquery for his row delivers the average education level for department B01.

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

Table 1. Result set for previous query

EMPNO	LASTNAME	WORKDEPT	EDLEVEL
000010	HAAS	A00	18
000030	KWAN	C01	20
000070	PULASKI	D21	16
000090	HENDERSON	E11	16
000110	LUCCHESSI	A00	19
000160	PIANKA	D11	17
000180	SCOUTTEN	D11	17
000210	JONES	D11	17
000220	LUTZ	D11	18
000240	MARINO	D21	17
000260	JOHNSON	D21	16
000280	SCHNEIDER	E11	17
000320	MEHTA	E21	16
000340	GOUNOT	E21	16
200010	HEMMINGER	A00	18
200220	JOHN	D11	18
200240	MONTEVERDE	D21	17
200280	SCHWARTZ	E11	17
200340	ALONZO	E21	16

 

Parent topic:

Correlated subqueries