Toad World Blog

Table Join Syntax

May 10, 2013 11:00:00 PM by Steve Hilker

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

TT_Optimization_TableJoinSyntax_Pic1.png-550x0

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.