Nested Table Expressions

An operand of a join can be more complex than the name of a single table or view. A subselect can be used as a complex operand in the FROM clause. Such an operand is called a nested table expression. Suppose we wanted to join the result of the last sample query with the SUBPART table, which includes a column named SUBPART (containing data similar to the commonly named PNO columns in the PART and PARTSUPP tables). The SUBPART table contains several rows with part numbers (SUBPART column) values that do not appear in either the PART or PARTSUPP tables.

The following sample query joins the SUBPART table with the result of the outer join of the PART and PARTSUPP table (three way join), displaying the nonmatching part number values from SUBPART in the result:

SELECT product, VALUE(subpart.subpart, partnum) AS
    partnum, pname, subpart.qty
  FROM subpart LEFT JOIN
  (SELECT VALUE(part.pno, partsupp.pno) AS
    partnum,pname, subpart.qty
    FROM part FULL OUTER JOIN partsupp
    ON part.pno = partsupp.pno) AS temp
  ON subpart.subpart = partnum

In this example, the correlation name is temp. The optional keyword AS is useful as an eye catcher for the correlation name that follows.

The following restrictions on nested table expressions apply:

• Must be enclosed in parentheses
• Must have a correlation name. In our example, the correlation name is temp.