Outer Join-to-Inner Join Conversion - Analytics Database - Teradata Vantage

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;