Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Query optimization and execution » Semantic query transformations


Conversion of outer joins to inner joins

The optimizer generates a left-deep processing tree for its access plans. The only exception to this rule is the existence of a right-deep nested outer join expression. The query execution engine's algorithms for computing LEFT or RIGHT OUTER JOINs require that preserved tables must precede null-supplying tables in any join strategy. So, the optimizer looks for opportunities to convert LEFT or RIGHT outer joins to INNER JOINs whenever possible, since INNER JOINs are commutable and give the optimizer greater degrees of freedom when performing join enumeration.

A LEFT or RIGHT OUTER JOIN is converted to an INNER JOIN if one of the following conditions is true:

This rewrite optimization can apply to an outer join query when the query refers to one or more views that are written using OUTER JOINs. The query WHERE clause may include conditions that restrict the output such that all null-supplying rows from one or more table expressions would be eliminated, making this optimization applicable.

 Example 1
 Example 2