Joining data from more than one table

Examples

Nested table expressions and user-defined table functions in joins

An operand of a join can be more complex than the name of a single table. You can specify one of the following items as a join operand:

nested table expression

A fullselect that is enclosed in parentheses and followed by a correlation name. The correlation name lets you refer to the result of that expression.

Using a nested table expression in a join can be helpful when you want to create a temporary table to use in a join. You can specify the nested table expression as either the right or left operand of a join, depending on which unmatched rows you want included.

user-defined table function

A user-defined function that returns a table.

Using a nested table expression in a join can be helpful when you want to perform some operation on the values in a table before you join them to another table.

Example: Using correlated references

In the following SELECT statement, the correlation name that is used for the nested table expression is CHEAP_PARTS. You can use this correlation name to refer to the columns that are returned by the expression. In this case, those correlated references are CHEAP_PARTS.PROD# and CHEAP_PARTS.PRODUCT.

SELECT CHEAP_PARTS.PROD#, CHEAP_PARTS.PRODUCT
   FROM (SELECT PROD#, PRODUCT
            FROM PRODUCTS
            WHERE PRICE < 10) AS CHEAP_PARTS;

The result table looks similar to the following output:

PROD#       PRODUCT
=====       ===========
505         SCREWDRIVER
30          RELAY

The correlated references are valid because they do not occur in the table expression where CHEAP_PARTS is defined. The correlated references are from a table specification at a higher level in the hierarchy of subqueries.

Example: Using a nested table expression as the right operand of a join

The following query contains a fullselect (in bold) as the right operand of a left outer join with the PROJECTS table. The correlation name is TEMP. In this case the unmatched rows from the PROJECTS table are included, but the unmatched rows from the nested table expression are not.

SELECT PROJECT, COALESCE(PROJECTS.PROD#, PRODNUM) AS PRODNUM,
       PRODUCT, PART, UNITS
FROM PROJECTS LEFT JOIN
      (SELECT PART,
          COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM,
          PRODUCTS.PRODUCT
       FROM PARTS FULL OUTER JOIN PRODUCTS
             ON PARTS.PROD# = PRODUCTS.PROD#) AS TEMP
   ON PROJECTS.PROD# = PRODNUM;

Example: Using a nested table expression as the left operand of a join

The following query contains a fullselect as the left operand of a left outer join with the PRODUCTS table. The correlation name is PARTX. In this case the unmatched rows from the nested table expression are included, but the unmatched rows from the PRODUCTS table are not.

SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
           FROM PARTS
           WHERE PROD# < '200') AS PARTX
        LEFT OUTER JOIN PRODUCTS
           ON PRODNUM = PROD#;

The result table looks similar to the following output:

PART        SUPPLIER         PRODNUM     PRODUCT
=======     ============     =======     ==========
WIRE        ACWF             10          GENERATOR
MAGNETS     BATEMAN          10          GENERATOR
OIL         WESTERN_CHEM     160         ----------

Because PROD# is a character field, Db2 does a character comparison to determine the set of rows in the result. Therefore, because the characters '30' are greater than '200', the row in which PROD# is equal to '30' does not appear in the result.

Example: Using a table function as an operand of a join

Suppose that CVTPRICE is a table function that converts the prices in the PRODUCTS table to the currency that you specify and returns the PRODUCTS table with the prices in those units. You can obtain a table of parts, suppliers, and product prices with the prices in your choice of currency by executing a query similar to the following query:

SELECT PART, SUPPLIER, PARTS.PROD#, Z.PRODUCT, Z.PRICE
   FROM PARTS, TABLE(CVTPRICE(:CURRENCY)) AS Z
   WHERE PARTS.PROD# = Z.PROD#;

Correlated references in table specifications in joins

Use correlation names to refer to the results of a nested table expression. After you specify the correlation name for an expression, any subsequent reference to this correlation name is called a correlated reference.

You can include correlated references in nested table expressions or as arguments to table functions. The basic rule that applies for both of these cases is that the correlated reference must be from a table specification at a higher level in the hierarchy of subqueries. You can also use a correlated reference and the table specification to which it refers in the same FROM clause if the table specification appears to the left of the correlated reference and the correlated reference is in one of the following clauses:

• A nested table expression that is preceded by the keyword TABLE
• The argument of a table function

For more information about correlated references, see Correlation names in references.

A table function or a table expression that contains correlated references to other tables in the same FROM clause cannot participate in a full outer join or a right outer join. The following examples illustrate valid uses of correlated references in table specifications.

In this example, the correlated reference T.C2 is valid because the table specification, to which it refers, T, is to its left.

SELECT T.C1, Z.C5
  FROM T, TABLE(TF3(T.C2)) AS Z
  WHERE T.C3 = Z.C4;

If you specify the join in the opposite order, with T following TABLE(TF3(T.C2), T.C2 is invalid.

In this example, the correlated reference D.DEPTNO is valid because the nested table expression within which it appears is preceded by TABLE, and the table specification D appears to the left of the nested table expression in the FROM clause.

SELECT D.DEPTNO, D.DEPTNAME,
  EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
  FROM DEPT D,
    TABLE(SELECT AVG(E.SALARY) AS AVGSAL,
            COUNT(*) AS EMPCOUNT
            FROM EMP E
            WHERE E.WORKDEPT=D.DEPTNO) AS EMPINFO;

If you remove the keyword TABLE, D.DEPTNO is invalid.