SQL rules for statements that contain join operations

Typically, DB2® performs a join operation first, before it evaluates the other clauses of the SELECT statement.

SQL rules dictate that the result of a SELECT statement look as if the clauses had been evaluated in this order:

FROM
WHERE
GROUP BY
HAVING
SELECT

A join operation is part of a FROM clause; therefore, for the purpose of predicting which rows will be returned from a SELECT statement that contains a join operation, assume that the join operation is performed first.

Example: Suppose that you want to obtain a list of part names, supplier names, product numbers, and product names from the PARTS and PRODUCTS tables. You want to include rows from either table where the PROD# value does not match a PROD# value in the other table, which means that you need to do a full outer join. You also want to exclude rows for product number 10. Consider the following SELECT statement:

SELECT PART, SUPPLIER,
  VALUE(PARTS.PROD#,PRODUCTS.PROD#) AS PRODNUM, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    WHERE PARTS.PROD# <> '10' AND PRODUCTS.PROD# <> '10';

The following result is not what you wanted:

PART        SUPPLIER       PRODNUM   PRODUCT
=======     ============   =======   ===========
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW

DB2 performs the join operation first. The result of the join operation includes rows from one table that do not have corresponding rows from the other table. However, the WHERE clause then excludes the rows from both tables that have null values for the PROD# column.

The following statement is a correct SELECT statement to produce the list:

SELECT PART, SUPPLIER,
  VALUE(X.PROD#, Y.PROD#) AS PRODNUM, PRODUCT
  FROM
    (SELECT PART, SUPPLIER, PROD# FROM PARTS WHERE PROD# <> '10') X
    FULL OUTER JOIN
    (SELECT PROD#, PRODUCT FROM PRODUCTS WHERE PROD# <> '10') Y
    ON X.PROD# = Y.PROD#;

For this statement, DB2 applies the WHERE clause to each table separately. DB2 then performs the full outer join operation, which includes rows in one table that do not have a corresponding row in the other table. The final result includes rows with the null value for the PROD# column and looks similar to the following output:

PART        SUPPLIER       PRODNUM   PRODUCT
=======     ============   =======   ===========
OIL         WESTERN_CHEM   160       -----------
BLADES      ACE_STEEL      205       SAW
PLASTIC     PLASTIK_CORP   30        RELAY
-------     ------------   505       SCREWDRIVER