Correlated Subqueries

All the previous examples of subqueries evaluated the innermost query completely before moving to the next level of the query. Some queries, however, cannot be completely evaluated before the outer, or main, query is evaluated. Instead, the search condition of a subquery depends on a value in each row of the table named in the outer query. Therefore, the subquery is evaluated repeatedly, once for each row selected from the outer table. This type of subquery is referred to as a correlated subquery.

Example: Retrieve the name, department, and salary, of any employee whose salary is above average for his or her department.

Enter this query:

SELECT   POSSIBLE.ENAME, POSSIBLE.DEPT, POSSIBLE.SALARY
     FROM     EMPLOYEE POSSIBLE
     WHERE    SALARY >
       (SELECT   AVG (SALARY)
        FROM     EMPLOYEE AVERAGE
        WHERE    POSSIBLE.DEPT = AVERAGE.DEPT) ;

Syntax Notes:

• The outer SELECT-FROM-WHERE block is the main query.

• The inner SELECT-FROM-WHERE block in parentheses is the subquery.

• POSSIBLE (following EMPLOYEE in the outer query) and AVERAGE (following EMPLOYEE in the subquery) are alias table names for the EMPLOYEE table, so that the information may evaluated as though it comes from two different tables.

It results in:

ENAME               DEPT   SALARY
  Krinski, Wanda      LIB    32500
  Brown, Penelope     MKT    37500
  Sanchez, Carla      MKT    35000
  Jones, David        RND    37500

The column AVERAGE.DEPT correlates with POSSIBLE.DEPT in the main, or outer, query. In other words, the average salary for a department is calculated in the subquery using the department of each employee from the table in the main query (POSSIBLE). The subquery computes the average salary for this department and then compares it with a row in the POSSIBLE table. If the salary in the POSSIBLE table is greater than the average salary for the department, then that employee's name, department, and salary are displayed.

The process of the correlated subquery works in the following manner. The department of the first row in POSSIBLE is used in the subquery to compute an average salary. Let's take Krinksi's row, whose department is the corporate library (LIB). In effect, the subquery is:

SELECT   AVG (SALARY)
     FROM     EMPLOYEE AVERAGE
     WHERE    'LIB' = AVERAGE.DEPT ;

LIB is the value from the first row in POSSIBLE, as alias for EMPLOYEE.

This pass through the subquery results in a value of $27,250, the average salary for the LIB dept. In the outer query, Krinski's salary of $32,500 is compared with the average salary for LIB; since it is greater, Krinski's name is displayed.

This process continues; next, Aster's row in POSSIBLE is evaluated, where MKT is the department. This time the subquery is evaluated as follows:

SELECT   AVG (SALARY)
     FROM     EMPLOYEE AVERAGE
     WHERE    'MKT' = AVERAGE.DEPT ;

The results of this pass through the subquery is an average salary of $34,833 for MKT, the Product Marketing and Sales Department. Since Aster has a salary of $32,000, a figure lower than the average, this record is not displayed.

Every department in POSSIBLE is examined in a similar manner before this subquery is completed.