outer join operation if it evaluates to FALSE  or UNKNOWN  for any NULL -complemented row generated for the operation. 

Thus, for this outer join: 

T1 LEFT JOIN T2 ON T1.A=T2.A 

Conditions such as these are null-rejected because they cannot be true for any NULL -complemented row (with T2  columns set to NULL ): 

T2.B IS NOT NULL T2.B > 3 T2.C <= T1.C T2.B < 2 OR T2.C > 1 

Conditions such as these are not null-rejected because they might be true for a NULL -complemented row: 

T2.B IS NULL T1.B < 3 OR T2.B IS NOT NULL T1.B < 3 OR T2.B > 3 

The general rules for checking whether a condition is null-rejected for an outer join operation are simple: 

•It is of the form A IS NOT NULL , where A  is an attribute of any of the inner tables 

•It is a predicate containing a reference to an inner table that evaluates to UNKNOWN  when one of its arguments is NULL 

•It is a conjunction containing a null-rejected condition as a conjunct 

•It is a disjunction of null-rejected conditions 

A condition can be null-rejected for one outer join operation in a query and not null-rejected for another. In this query, the WHERE  condition is null-rejected for the second outer join operation but is not null-rejected for the first one: 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T1.B WHERE T3.C > 0 

If the WHERE  condition is null-rejected for an outer join operation in a query, the outer join operation is replaced by an inner join operation. 

For example, in the preceding query, the second outer join is null-rejected and can be replaced by an inner join: 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T1.B WHERE T3.C > 0 

For the original query, the optimizer evaluates only plans compatible with the single table-access order T1,T2,T3 . For the rewritten query, it additionally considers the access order T3,T1,T2 . 

A conversion of one outer join operation may trigger a conversion of another. Thus, the query: 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0 

Is first converted to the query: 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A 

