Joining data from more than one table
Sometimes the information that you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table.
About this task
You can use a SELECT statement to retrieve and join column values from two or more tables into a single row.
A join operation typically matches a row of one table with a row of another on the basis of a join condition. DB2® supports the following types of joins: inner join, left outer join, right outer join, and full outer join. You can specify joins in the FROM clause of a query.
- 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.
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.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;
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.SELECT PART, SUPPLIER, PARTS.PROD#, Z.PRODUCT, Z.PRICE
FROM PARTS, TABLE(CVTPRICE(:CURRENCY)) AS Z
WHERE PARTS.PROD# = Z.PROD#;
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.
- A nested table expression that is preceded by the keyword TABLE
- The argument of a table function
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.
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.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.