Nested table expressions and LATERAL in DB2 SQL

Nested table expressions

A fullselect in parentheses in FROM is a nested table expression. The result table is whatever that fullselect would return if executed. Columns need not all have unique names, but you cannot reference a non-unique name. A correlation name is required.

1
2
3
4
5
6
SELECT D.DEPTNO, D.DEPTNAME, A.AVGSAL
  FROM DSN8C10.DEPT D,
       (SELECT WORKDEPT, AVG(SALARY) AS AVGSAL
          FROM DSN8C10.EMP
         GROUP BY WORKDEPT) AS A
 WHERE D.DEPTNO = A.WORKDEPT;

IBM’s reasons to use one instead of a view: you do not want a permanent shared object, or the inner query depends on host variables. A nested table can also compute something you cannot filter in the same WHERE as an aggregate (for example rank first, then keep rank <= 3)—IBM notes a CTE could do that too.

Without extra keywords, correlated references inside the nested fullselect must come from a table-reference at a higher level in the subquery hierarchy (an outer query), not from a neighbor in the same FROM list.

Lateral references and the TABLE keyword

Table functions may contain correlated references to other tables in the same FROM clause if those tables precede them left to right. The same capability exists for nested table expressions if you specify the optional keyword TABLE. Otherwise only higher-level correlation is allowed.

Later Db2 for z/OS levels also accept the standard keyword LATERAL for the same idea. Think: “for each left row, run this table-valued query.”

1
2
3
4
5
6
7
8
9
10
11
-- For each department, the single highest-paid employee (lateral)
SELECT D.DEPTNO, D.DEPTNAME, E.EMPNO, E.LASTNAME, E.SALARY
  FROM DSN8C10.DEPT D
  LEFT JOIN TABLE (
         SELECT EMPNO, LASTNAME, SALARY
           FROM DSN8C10.EMP
          WHERE WORKDEPT = D.DEPTNO
          ORDER BY SALARY DESC
          FETCH FIRST 1 ROW ONLY
       ) AS E
    ON 1 = 1;

IBM’s validity rule: the correlated nested table or table function:

• May participate in INNER JOIN or LEFT OUTER JOIN if referenced tables precede it.
• Must not participate in FULL OUTER JOIN or RIGHT OUTER JOIN.

IBM’s invalid example places the TABLE function before the table it references. Valid form: name T first, then TABLE(func(T.C2)). Left-to-right order is part of the syntax contract, not a style choice.

Table functions

A table-function-reference in FROM is the set of rows the function returns. User-defined table functions (external or SQL) and some built-in functions belong here. You often write TABLE(function(args)) AS corr. Arguments can be constants, host variables, or—when lateral—columns from a preceding table. Generic table functions need a typed correlation clause that names the result columns.

Table functions are how you expose a program or algorithm as a table: parse a string into rows, call a search engine, explode a structure. Cost is optimizer-visible only as far as statistics and CARDINALITY clauses allow—do not assume they are “free.”

XML table expressions (XMLTABLE)

XMLTABLE is the built-in table function that turns XML into relational rows. You pass an XPath/XQuery row pattern and column definitions. The result joins like any other table reference.

1
2
3
4
5
6
7
8
SELECT X.EMPNO, X.SALARY
  FROM MY.EMP_XML T,
       XMLTABLE(
         '$d/employee' PASSING T.XMLDOC AS "d"
         COLUMNS
           EMPNO  CHAR(6)  PATH '@id',
           SALARY DECIMAL(9,2) PATH 'salary'
       ) AS X;

Pair XMLTABLE with XMLEXISTS when you only need to test whether a node exists, and with XMLTABLE when you need columns. Encoding, namespaces, and XML column type rules are their own topic; here the point is that XMLTABLE is a FROM object, not a scalar function.

Collection-derived tables (UNNEST)

UNNEST turns an array into a table of elements (and optionally ordinality). That is the collection-derived table in the SQL Reference. Use it when a procedure receives an array of keys and you want to join to EMP rather than loop.

1
2
3
4
SELECT E.EMPNO, E.LASTNAME
  FROM UNNEST(:EMPNO-ARRAY) AS U(EMPNO)
  INNER JOIN DSN8C10.EMP E
    ON E.EMPNO = U.EMPNO;

Arrays and UNNEST require types and bind options that support arrays. If your shop has no array types yet, a nested VALUES table or a DGTT of keys is the older equivalent.

Self joins

A self join is the same table (or view) listed twice with different correlation names. Classic examples: employee and manager both in EMP; department and administering department both in DEPT.

1
2
3
4
5
6
7
8
9
SELECT E.EMPNO AS EMP,
       E.LASTNAME,
       M.EMPNO AS MGR,
       M.LASTNAME AS MGRNAME
  FROM DSN8C10.EMP E
  LEFT JOIN DSN8C10.EMP M
    ON E.WORKDEPT = M.WORKDEPT
   AND M.JOB = 'MANAGER'
   AND E.EMPNO <> M.EMPNO;

Qualify every column. Without aliases, EMPNO is ambiguous and the statement fails. Self joins are ordinary joins; they can be inner or outer, and they can be nested with other tables.

Multiple joins and nested joins

You can join three or more tables in one FROM list. Parentheses around a joined-table control how outer joins associate: (A LEFT JOIN B) JOIN C is not always the same as A LEFT JOIN (B JOIN C). Build two-table results first, check counts, then add the next table.

Nested joins also appear when a nested table expression itself contains joins. That is fine; keep correlation names unique across the whole statement so E is not reused for two different EMP instances.

Join predicates versus WHERE

ON is the join predicate: which pairs match. WHERE filters the join result. For inner joins they often behave alike. For outer joins, a WHERE on the null-supplying side drops unmatched rows and secretly turns the join into an inner join. Put “optional side” filters in ON; put “must keep this preserved row” filters in WHERE on the preserved table.

Join ordering and join elimination

Join order is the sequence in which Db2 combines tables. The optimizer chooses it from cost, statistics, and join type—not from the order you typed inner joins. Outer joins and LATERAL/TABLE correlation constrain the order: you cannot probe a nested table before its left input exists. EXPLAIN (PLAN_TABLE QBLOCKNO, PARENT_QBLOCKNO, JOIN_TYPE, METHOD) is how you see what happened.

Join elimination is an optimizer rewrite that removes a join that cannot change the answer. A common case: you join a child to a parent only to prove the foreign key is valid, you select no parent columns, and referential integrity is enforced. Db2 may drop the parent access. That is a feature. If you needed the join to filter orphans and RI is not actually enforced (or you use informational RI), do not rely on elimination to do your data-quality job—write NOT EXISTS or an outer join you inspect.

Materialization of a nested table or view is the opposite instinct: sometimes Db2 must build the inner result before joining. EXPLAIN and performance manuals discuss when nested table expressions materialize. Huge nested aggregates joined back to a large table deserve a look at work-file use.

Explain It Like I'm Five

A nested table is a homemade placemat of rows you slide under the real table so you can treat a query as if it were another table. LATERAL/TABLE is asking, for each kid on the left, “please fetch that kid’s favorite snack list.” XMLTABLE is opening a lunchbox of XML and laying the sandwiches in rows. UNNEST is dumping a bag of marbles into a tray, one marble per row. A self join is two name tags on the same class list: “kid” and “best friend.” The optimizer decides who holds hands first (join order) and may skip a parent table if the teacher already promised every kid has a parent (join elimination).

Exercises

1. Write a nested table that computes AVG(SALARY) by WORKDEPT and join it to DEPT.
2. Rewrite “top earner per department” with TABLE (… FETCH FIRST 1 ROW ONLY) correlated to DEPT.
3. Write a self join of DEPT to itself matching DEPTNO to ADMRDEPT.
4. Explain why TABLE (SELECT … WHERE t.col = …) AS X cannot sit to the left of T in FROM.
5. Give one reason Db2 might eliminate a join to DEPT when querying EMP.

Frequently asked questions

Nested table or CTE?

Use a CTE when you reference the result twice or want named steps. Use a nested table when the subquery is local to one join slot, especially with TABLE/LATERAL. Both are fullselects in a trench coat.

Is comma FROM with TABLE the same as LEFT JOIN TABLE?

Comma is a cross/inner-style join: if the nested table returns no rows, the left row disappears. LEFT JOIN TABLE … ON 1=1 preserves the left row (nulls on the right), which is what you want for “optional top employee.”

Can XMLTABLE be lateral?

Yes—XMLTABLE is a table function, so it can take a preceding table’s XML column as PASSING input in the same FROM clause, following table-function correlation rules.