17.10 - Exclusion Product Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

Exclusion Product Join Process

  1. For each left table row, read all right table rows from the beginning until one is found that can be joined with it.
  2. Produce the join result.

    If no matching right table rows are found, return the left row.

Vantage does not support dynamic row partition elimination for exclusion product joins for 8-byte partitioning.

Exclusion Product Join Example

The following request returns the names of those employees who do not work in Chicago:

FROM employee
WHERE dept_no NOT IN (SELECT dept_no
                      FROM department
                      WHERE loc = ’CHI’);

Because the subquery returns only one row, the Optimizer selects an exclusion product join for the join plan using with the following process:

  1. All AMPs are searched for department rows where loc = ‘CHI’.
    • If only one AMP is selected, and if loc is an index, then an all-AMPs retrieve is not performed.
    • The spool containing the single department row is duplicated on every AMP that contains the spooled employee rows.
  2. The single row found to satisfy this condition, that for department 600, is duplicated right away, without being spooled in the local AMP.
  3. The rows in the two spools undergo an exclusion product join on each AMP.
  4. Name information for any employee row whose dept_no is not 600 is placed in a result spool on each AMP.
  5. When the last AMP has completed its portion of the join, the contents of all results spools are sent to the requesting application via a BYNET merge.

The following graphic illustrates this process:

Exclusion product join example