15.10 - Outer Join-to-Inner Join Conversion - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

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;

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 comma syntax is referred to as releasing the join. A join can be released only if all joins in its block can be released.

    Releasing inner joins presents one canonical form of inner joins to the Join Planner. This guarantees consistent plans for both inner join syntaxes and eliminates the need for duplicate code to handle them both.