DB2 subquery forms in depth

Fullselect

IBM layers a query like this:

A subquery is a fullselect in parentheses. That means the inner query may itself contain UNION:

1
2
3
4
5
6
7
SELECT LASTNAME, FIRSTNME, SALARY
  FROM DSN8C10.EMP
 WHERE EMPNO = (
       SELECT RESPEMP FROM PROJA1 WHERE MAJPROJ = 'SECRET'
       UNION
       SELECT RESPEMP FROM PROJA2 WHERE MAJPROJ = 'SECRET'
       );

The inner fullselect is still a scalar context: one column, at most one row after UNION’s duplicate elimination. Two different RESPEMP values still cause SQLCODE -811.

Set operators inside a subquery follow the same compatibility rules as a standalone UNION: same degree, compatible types. ORDER BY belongs on the select-statement (outer query), not on each inner subselect, unless the inner query is wrapped as a nested table expression that itself is a select-statement in contexts that allow it. Scalar fullselects may use ORDER BY with FETCH FIRST 1 ROW ONLY to pick a deterministic row.

Scalar subqueries

A scalar-fullselect returns a single column value from a single row. Empty → null. More than one row → error. Use it anywhere an expression is allowed, except IBM’s banned list: CHECK, CREATE VIEW WITH CHECK OPTION, CALL input arguments, most aggregate arguments, ORDER BY, GROUP BY, and join ON.

1
2
3
4
5
6
SELECT DEPTNO,
       DEPTNAME,
       (SELECT COUNT(*)
          FROM DSN8C10.EMP E
         WHERE E.WORKDEPT = D.DEPTNO) AS EMP_COUNT
  FROM DSN8C10.DEPT D;

COUNT(*) is a scalar aggregate: one row even when the department has zero employees (count is 0, not null). A non-aggregated (SELECT EMPNO FROM EMP WHERE WORKDEPT = D.DEPTNO) would be -811 for any department with two people.

Uncorrelated scalar

1
2
3
SELECT EMPNO, SALARY
  FROM DSN8C10.EMP
 WHERE SALARY > (SELECT AVG(SALARY) FROM DSN8C10.EMP);

Correlated scalar

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

Table subqueries

A nested table expression is a fullselect in FROM. It is a table, not a scalar. You must provide a correlation name to qualify its columns in most useful queries.

1
2
3
4
5
6
7
8
SELECT D.DEPTNO, D.DEPTNAME, A.AVG_SAL, A.N
  FROM DSN8C10.DEPT D
  INNER JOIN (
        SELECT WORKDEPT, AVG(SALARY) AS AVG_SAL, COUNT(*) AS N
          FROM DSN8C10.EMP
         GROUP BY WORKDEPT
       ) AS A
    ON D.DEPTNO = A.WORKDEPT;

This is often clearer than a correlated scalar in the SELECT list and gives the optimizer a join to a grouped result. Column names inside the nested query that are not unique cannot be referenced from outside.

TABLE keyword and lateral references

Write TABLE (fullselect) when the nested query must see columns from table-references to its left in the same FROM clause (lateral correlation). Without TABLE, the nested expression is not allowed to search the hierarchy above itself for those names.

1
2
3
4
5
6
7
8
SELECT D.DEPTNO, X.EMPNO, X.LASTNAME
  FROM DSN8C10.DEPT D,
       TABLE (
         SELECT EMPNO, LASTNAME
           FROM DSN8C10.EMP E
          WHERE E.WORKDEPT = D.DEPTNO
          FETCH FIRST 3 ROWS ONLY
       ) AS X;

Each department contributes up to three employees. That is a table subquery with a correlated reference to D. A JOIN LATERAL in other dialects is this TABLE () pattern on z/OS.

Correlated subqueries

Correlation is not a separate syntax—it is a reference rule. Q.C is correlated when it appears in the subquery, Q is not in that subquery’s FROM, and Q names a higher-level table. Unique correlation names are recommended because EMP can appear at many levels.

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

DELETE/UPDATE targets are the top of the hierarchy; qualify them when the subquery must see the current row:

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

Matching form to predicate

• = (fullselect) — scalar; one row
• IN (fullselect) — one column, many rows; = ANY
• NOT IN — <> ALL; inner nulls can make the predicate UNKNOWN
• EXISTS — any columns; never UNKNOWN
• > ALL / ANY / SOME — quantified; empty ALL is true, empty ANY is false

Subqueries versus joins versus data-change FROM

A nested table expression in FROM is still a query, not a data change. FINAL TABLE (INSERT …) is a different table-reference: it must stand alone in FROM and it modifies data. Do not mix them in one FROM list.

Rewrites: correlated EXISTS ↔ semi-join; scalar correlated aggregate ↔ join to grouped nested table; IN uncorrelated ↔ join (watch duplicates). Prefer the form whose cardinality matches the business rule (error on two matches vs extra rows).

Explain It Like I'm Five

A fullselect is a whole recipe (and you can tape two recipes together with UNION). If you use the recipe as a number (how many cookies?), that is scalar—you are allowed one number. If you use the recipe as a tray of cookies to sit beside another tray (FROM), that is a table subquery. Correlation is asking about this child’s tray, not the whole school’s.

Exercises

1. Label each inner query as scalar, predicate (IN/EXISTS), or table subquery in three statements you write yourself.
2. Rewrite a correlated COUNT scalar in the SELECT list as a join to a grouped nested table.
3. Write a TABLE () lateral example that returns the highest-paid employee per department (FETCH FIRST 1 ROW ONLY with ORDER BY SALARY DESC).
4. Explain why a scalar subquery cannot be a grouping-expression.
5. Convert NOT IN (SELECT nullable_col …) to NOT EXISTS and say why.