Exclusion Join Types | Join Planning/Optimization | Teradata Vantage - 17.10 - Exclusion Join - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Exclusion Join -- SQL Operators That Often Cause an Exclusion Join Operation

Exclusion join is a product, merge, or hash join where only the rows that do not satisfy (are NOT IN) any condition specified in the request are joined.

In other words, exclusion join finds rows in the first table that do not have a matching row in the second table.

Exclusion join is an implicit form of the outer join.

Exclusion product joins with dynamic row partition elimination are not supported for8-byte partitioning.

Also see Inclusion and Exclusion Product Joins With Dynamic Row Partition Elimination.

The following SQL specifications frequently cause the Optimizer to select an exclusion join:
  • Use of the NOT IN logical operator in a subquery.
  • Use of the EXCEPT and MINUS set operators.

Exclusion Joins and NULLABLE Columns

To avoid returning join rows that are null on the join column, use one of the following methods:
  • When you create a table, define any columns that might be used for NOT IN join conditions as NOT NULL.
  • When you write a query, qualify a potentially problematic join with an IS NOT NULL specification. For example:
    WHERE Customer.CustAddress IS NOT NULL

Types of Exclusion Join