In certain cases, an outer join in a query block can be converted to an inner join.
If a SELECT request specifies NORMALIZE, Query Rewrite does not push it to derived tables for a temporal outer join rewrite.
Consider the following example:
SELECT DISTINCT product_name FROM product LEFT OUTER JOIN sales ON product_key=sales_product_key WHERE quantity > 10 ;
The outer join in this request can be safely converted to an inner join because the subsequent WHERE predicate filters any non-matching rows.
Similarly, the outer join in the following request can be converted to an inner join because the ON clause predicate b2 < 10 is false if b2 is null, and therefore the condition removes all non-matching rows of the outer join.
SELECT * FROM t1 LEFT OUTER JOIN t2 ON a1=a2 WHERE b2 < 10;
Rules for Converting Outer Joins to Inner Joins
As a general rule, an outer join can be converted to an inner join if there is a condition on the inner table that filters out non-matching rows. In a left outer join, the right table is the inner table, while it is the left table in a right outer join. In a full outer join, both tables are inner tables. Conditions that are FALSE for nulls are referred to as null filtering conditions, and these are the conditions that enable the outer-to-inner join conversion to be made.
An outer join can be converted to an inner join if at least one of the following conditions is true:
- The WHERE clause contains at least one null filtering condition on the inner table.
- The outer join is involved in another join, and the other join condition has one or more null filtering conditions on the inner table. The other join in this case can be an inner join, left outer join, or right outer join. It cannot be a full outer join because there is no inner table in this case.
A null filtering condition on the right side of a full outer join converts it to a left outer join, while a null filtering condition on the left side converts it to a right outer join.
This rewrite also releases the join by converting the inner join syntax to comma syntax.
SELECT DISTINCT product_name FROM product INNER JOIN sales ON product_key = sales_product_key WHERE quantity > 10;
The outer-to-inner join conversion rule rewrites this request as follows:
SELECT DISTINCT product_name FROM product, sales WHERE quantity > 10 AND product_key = sales_product_key;
The conversion of a join from INNER join syntax to the equivalent comma syntax presents one canonical form of inner joins to the Join Planner. This guarantees consistent plans for both INNER join and comma syntaxes and eliminates the need for duplicate code to handle them both.