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

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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.