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:

a null-intolerant predicate referencing columns of the null-supplying tables is present in the query WHERE clause. Since this predicate is null-intolerant, any all-NULL row that would be produced by the OUTER join is eliminated from the result, making the query semantically equivalent to an inner join.
the null-supplying side of an OUTER JOIN returns exactly one row for each row from the preserved side. If this condition is true, there are no null-supplied rows and the OUTER JOIN is equivalent to an INNER join.

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

For the query below, for each row of the SalesOrderItems table there is exactly one row that matches the Products table because the ProductID column is declared not NULL and the SalesOrderItems table has the following foreign key: "FK_ProductID_ID" ("ProductID") REFERENCING "Products" ("ID").

The following SELECT statements show how the query is rewritten after a rewrite optimization:

SELECT * FROM SalesOrderItems s LEFT OUTER JOIN Products p ON (p.ID = s.ProductID);
SELECT * FROM SalesOrderItems s JOIN Products p ON (p.ID = s.ProductID);

Example 2

The following query lists products and their corresponding orders for larger quantities; the LEFT OUTER JOIN ensures that all products are listed, even if they have no orders:

SELECT *
FROM Products p KEY LEFT OUTER JOIN SalesOrderItems s
WHERE s.Quantity > 15;

The problem with this query is that the predicate in the WHERE clause eliminates any product with no orders from the result because the predicate s.Quantity > 15 is interpreted as FALSE if s.Quantity is NULL. The query is semantically equivalent to:

SELECT *
FROM Products p KEY JOIN SalesOrderItems s
WHERE s.Quantity > 15;

This rewritten form is the query that the database server optimizes.

In this example, the query is almost certainly written incorrectly; it should instead be:

SELECT *
FROM Products p
    KEY LEFT OUTER JOIN SalesOrderItems s
          ON s.Quantity > 15;

In this way, the test of Quantity is part of the outer join condition. You can demonstrate the difference in the two queries by inserting some new products into the Products table for which there are no orders and then executing the queries again.

INSERT INTO Products
SELECT ID + 10, Name, Description,
        'Extra large', Color, 50, UnitPrice, Photo
FROM Products
WHERE Name = 'Tee Shirt';

Discuss this page in DocCommentXchange.