ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Conversion of outer joins to inner joins

For the most part, 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. Consequently 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 can be converted to an inner join when a null-intolerant predicate on the null-supplying table is present in the query's WHERE clause. Since this predicate is null-intolerant, any all-NULL row that would be produced by the outer join will be eliminated from the result, hence making the query semantically equivalent to an inner join.

Example 

For example, consider the query

SELECT *
FROM product p KEY LEFT OUTER JOIN sales_order_items s
WHERE s.quantity > 15

which is intended to list all products and their orders for larger quantities; the LEFT OUTER JOIN is intended to ensure that all products are listed, even if they have no orders. The problem with this query is that the predicate in the WHERE clause will eliminate from the result any product with no orders, because the predicate s.quantity > 15 will be interpreted as FALSE if s.quantity is NULL. Hence the query is semantically equivalent to

SELECT *
FROM product p KEY JOIN sales_order_items s
WHERE s.quantity > 15

and it is this rewritten form of the query that the server will optimize.

In this example, the query is almost certainly written incorrectly; it should probably read

SELECT *
FROM product p
    KEY LEFT OUTER JOIN sales_order_items s
          ON s.quantity > 15

so that the test of quantity is part of the outer join condition.

While it is rare for this optimization to apply to straightforward outer join queries, it can often apply when a query refers to one or more views that are written using outer joins. The query's WHERE clause may include conditions that restrict the output of the view such that all null-supplying rows from one or more table expressions would be eliminated, hence making this optimization applicable.