Correlated subqueries in DB2 SQL

Uncorrelated subquery

An uncorrelated subquery does not mention outer-row values. For every employee, “employees on project MA2111” is the same inner question:

1
2
3
4
5
6
7
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8C10.EMP
 WHERE EMPNO IN (
       SELECT EMPNO
         FROM DSN8C10.EMPPROJACT
        WHERE PROJNO = 'MA2111'
       );

Db2 can run the inner SELECT once, remember the EMPNO set, and test membership. The inner WHERE only uses EMPPROJACT columns and a literal.

Correlated subquery

A correlated subquery is reevaluated when Db2 examines a new outer row (WHERE) or a new group (HAVING). It contains a correlated reference: a column of a table identified at a higher level.

IBM’s sample: list employees whose education level is greater than the average education of their own department—not the company average.

1
2
3
4
5
6
7
SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL
  FROM DSN8C10.EMP X
 WHERE EDLEVEL > (
       SELECT AVG(EDLEVEL)
         FROM DSN8C10.EMP
        WHERE WORKDEPT = X.WORKDEPT
       );

X is a correlation name for the outer EMP. X.WORKDEPT in the inner WHERE is the correlated reference. When the outer row is Christine in department A00, the inner query is AVG(EDLEVEL) WHERE WORKDEPT = 'A00'. When the outer row is Michael in B01, the inner query uses B01. An uncorrelated company-wide AVG(EDLEVEL) would be the wrong business question.

Correlation names and qualified references

Because the same table can appear at many levels, IBM recommends unique correlation names and qualified columns. A qualified name Q.C is a correlated reference only if all three are true:

• Q.C appears in a search condition or select list of a subquery
• Q does not name a table in that subquery’s FROM clause
• Q does name a table used at a higher level

If Q names a table at more than one higher level, Q.C refers to the lowest level that contains the subquery. Unqualified correlated names are legal but poor practice: name resolution walks outward and is easy to misread.

1
2
3
4
5
6
7
8
9
-- Outer EMP is X; inner EMP is unqualified local columns
SELECT EMPNO
  FROM DSN8C10.EMP X
 WHERE EXISTS (
       SELECT *
         FROM DSN8C10.EMP
        WHERE X.WORKDEPT = WORKDEPT
          AND SALARY < 20000
       );

Inner WORKDEPT and SALARY belong to the inner EMP (local FROM). X.WORKDEPT belongs to the outer row. The query lists employee numbers of people who work in a department that has at least one employee earning less than 20000—including possibly themselves.

Correlated versus uncorrelated

You can often rewrite a correlated subquery as a join (sometimes with GROUP BY on the inner grain). Correctness first: a join can duplicate outer rows if the inner match is one-to-many; EXISTS never duplicates the outer row. That is a practical reason EXISTS is popular for “parent has children” filters.

EXISTS versus IN

IN — membership in a set of values

IN wants a value list (one column). It shines when the inner query is uncorrelated or when you think “is this key in that set?”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Uncorrelated IN: membership in a fixed project roster
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE EMPNO IN (
       SELECT EMPNO FROM DSN8C10.EMPPROJACT
        WHERE PROJNO = 'MA2111'
       );
 
-- Correlated IN is possible but often rewritten as EXISTS
SELECT DEPTNO, DEPTNAME
  FROM DSN8C10.DEPT D
 WHERE DEPTNO IN (
       SELECT WORKDEPT FROM DSN8C10.EMP E
        WHERE E.WORKDEPT = D.DEPTNO
          AND E.JOB = 'MANAGER'
       );

The second form correlates D.DEPTNO into the inner query. EXISTS is usually clearer for that “does a matching employee exist?” reading:

1
2
3
4
5
6
7
8
SELECT DEPTNO, DEPTNAME
  FROM DSN8C10.DEPT D
 WHERE EXISTS (
       SELECT *
         FROM DSN8C10.EMP E
        WHERE E.WORKDEPT = D.DEPTNO
          AND E.JOB = 'MANAGER'
       );

EXISTS — presence of rows

EXISTS (fullselect) is true when the inner row count is not zero, false when it is zero, and cannot be unknown. The select list is ignored; use SELECT *. There is no built-in keyword “NOT EXISTS” as part of the predicate name; you write the logical operator NOT in front of EXISTS.

EXISTS can contain UNION ALL of several inner sources, each correlated:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT C.SNO
  FROM CUST C
 WHERE C.STATE = 'CA'
   AND EXISTS (
       SELECT * FROM MONTH1
        WHERE DATE BETWEEN '01/01/2009' AND '01/31/2009'
          AND C.SNO = SNO
       UNION ALL
       SELECT * FROM MONTH2
        WHERE DATE BETWEEN '02/01/2009' AND '02/28/2009'
          AND C.SNO = SNO
       UNION ALL
       SELECT * FROM MONTH3
        WHERE DATE BETWEEN '03/01/2009' AND '03/31/2009'
          AND C.SNO = SNO
       )
 ORDER BY C.SNO;

NOT EXISTS versus NOT IN

“Departments with no employees” is an anti-join. Prefer NOT EXISTS:

1
2
3
4
5
6
DELETE FROM DEPARTMENT THIS
 WHERE NOT EXISTS (
       SELECT *
         FROM EMPLOYEE
        WHERE WORKDEPT = THIS.DEPTNO
       );

THIS is the correlation name for the DELETE target (the highest level of a DELETE is the target table, not a FROM clause). If you write DEPTNO NOT IN (SELECT WORKDEPT FROM EMPLOYEE) and WORKDEPT can be null, a null in the inner result makes NOT IN UNKNOWN, and WHERE keeps nothing—so you might delete zero rows when you expected to delete empty departments. NOT EXISTS stays true/false.

When correlation is needed

Use a correlated reference when the inner question is about the current outer row or group:

• Per-parent aggregate — salary versus this department’s average, not the company average
• Existence of children — this customer has an order; this dept has a manager
• Absence of children — NOT EXISTS for this key
• Per-group HAVING — this group’s COUNT compared with a subquery that uses the grouping column
• SELECT-list scalar lookup — price for this PART

Skip correlation when the inner set is global or constant: “in this project number,” “greater than the company average,” “IN a literal list.” Forcing correlation on a global query (WHERE 1 = 1 AND outer.col = outer.col inside the inner query) does not help and can confuse readers and the optimizer.

If you can state the result as a join of two sets without per-row inner aggregates, a join (or JOIN to a grouped nested table) is often easier to test. Keep the subquery when you need EXISTS semantics (no row multiplication) or a scalar that must error on duplicates (-811).

HAVING and correlation

Correlation in HAVING refers to the current group. A correlated reference to that group must identify a grouping column or be inside a column function.

1
2
3
4
5
6
7
8
9
SELECT WORKDEPT, AVG(SALARY) AS DEPT_AVG
  FROM DSN8C10.EMP E
 GROUP BY WORKDEPT
HAVING AVG(SALARY) > (
       SELECT AVG(SALARY)
         FROM DSN8C10.EMP M
        WHERE M.JOB = 'MANAGER'
          AND M.WORKDEPT = E.WORKDEPT
       );

E.WORKDEPT is the grouping column of the outer group. The inner query is “average manager salary in this department.” Departments with no manager get a null inner average; the comparison is UNKNOWN; HAVING drops those groups.

UPDATE and DELETE with correlation

Searched UPDATE and DELETE use the target table as the top of the hierarchy. Qualify the target with a correlation name when the subquery must see the current row:

1
2
3
4
5
6
7
8
UPDATE DSN8C10.EMP E
   SET SALARY = SALARY * 1.05
 WHERE EXISTS (
       SELECT *
         FROM DSN8C10.EMPPROJACT P
        WHERE P.EMPNO = E.EMPNO
          AND P.PROJNO LIKE 'MA%'
       );

Without E.EMPNO, an inner EMPNO would be ambiguous or would not mean “this employee.”

Explain It Like I'm Five

Uncorrelated is one shared shopping list for the whole class: “everyone whose number is on the project list.” Correlated is walking to each child and asking a question about that child: “is your score higher than the average of your table?” EXISTS is peeking in a backpack: “is there at least one sandwich?” You do not care what the sandwich is. IN is checking whether your lunch ticket number appears on a posted list.

Exercises

1. Write a correlated subquery that lists employees who earn more than the average salary of their own WORKDEPT.
2. Rewrite that query as a join to a grouped nested table of department averages. Note any duplicate-row differences.
3. List departments that have no employees using NOT EXISTS. Explain why NOT IN (SELECT WORKDEPT FROM EMP) can misbehave if WORKDEPT is nullable.
4. Mark X.WORKDEPT in the EDLEVEL example as the correlated reference and say what value it has when the outer row is in department D11.
5. Decide for each request: uncorrelated IN, correlated EXISTS, or scalar subquery — (a) employees on project ABCD, (b) customers who placed at least one order, (c) each employee with the company-wide max salary as an extra column.