Table Join Syntax

This tip covers how to specify the table join syntax which is used when SQL alternatives are generated.

When the syntax for SQL statements was originally developed, the conditions of joining tables were specified in the WHERE clause. The Ansi-92 SQL standard introduced specifying tables joins with the INNER JOIN, CROSS JOIN and OUTER JOIN with joining conditions in the FROM clause.

One technique for rewriting the syntax of the SQL statement is to try using different JOIN syntax.

Rewrite SQL using the Ansi-92 JOIN syntax

Specify to use the JOIN clause from the Ansi-92 SQL standard when generating the SQL alternatives. During the optimization, the SQL statement is converted to the Ansi-92 SQL standard and then the SQL syntax transformation rules are applied to rewrite the converted SQL statement. Next, the Oracle hints are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the join syntax from the original statement SQL, but these SQL alternatives are simply the original SQL with an Oracle hint applied.

The OUTER JOIN is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. So to avoid producing the wrong result set, the conversion of the OUTER JOIN syntax cannot be applied.

For example:

SELECT DPT_ID
   FROM EMPLOYEE
        INNER JOIN DEPARTMENT
           ON EMP_DEPT = DPT_ID

Rewrite SQL without using the Ansi-92 JOIN syntax

Specify to join tables in the FROM clause without the JOIN syntax or using a comma. The join analysis occurs in the WHERE clause which specifies that the column in one table is compared to a column in another table. During the optimization, the SQL statement is converted from the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL. Next, the Oracle hints are applied to the original SQL and the transformed SQL.  So you may see SQL alternatives that use the JOIN syntax from the original SQL statement, but these SQL alternatives are simply the original SQL with an Oracle hint applied.

The OUTER JOIN is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator.  So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.

For example:

SELECT DPT_ID
   FROM EMPLOYEE,
        DEPARTMENT WHERE DPT_ID = EMP_DEPT