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. By also applying the inner join rewrite, this request is rewritten as follows:
SELECT DISTINCT product_name FROM product, sales WHERE quantity > 10 AND product_key = sales_product_key;
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;