15.10 - Exclusion Merge Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

1 The left and right tables are distributed and sorted based on the row hash values of the join columns.

2 For each left table row, read all right table rows having the same row hash value until one is found that matches the join condition.

3 Produce the join result.

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

The following SELECT request is an example for which the Optimizer uses an exclusion merge join for its join plan:

     SELECT name 
     FROM employee 
     WHERE dept_no NOT IN (SELECT dept_no
                           FROM department
                           WHERE loc <> ‘CHI’);

The following stages document a concrete example of the exclusion join process:

1 All AMPs are searched for department rows where loc <> ' CHI '.

2 The multiple rows found to satisfy this condition, that for department 600, is placed in a spool on the same AMP.

3 The spool containing the single department row is redistributed.

4 The rows in the two spools undergo an exclusion merge join on each AMP.

5 Name information for any employee row whose dept_no is not 600 is placed in a result spool on each AMP.

When the last AMP has completed its portion of the join, the contents of all result spools are sent to the user by means of a BYNET merge.

The processing stages of an exclusion merge join are like those used in the exclusion product join (see “Exclusion Join” on page 462), with the following exceptions:

  • Multiple rows are retrieved in stage 2.
  • Stages 2 and 4 are combined in the exclusion merge join, and redistribution occurs instead of duplication.
  • Stage 3 is removed.
  • Stage 5 is changed to an exclusion product join.
  • Consider the following employee and customer tables for the next exclusion merge join example:


    The graphic that follows illustrates the row redistribution caused by the following SELECT request:

         SELECT name 
         FROM employee
         WHERE job_code = 512101
         AND   e_num NOT IN (SELECT sales_enum
                             FROM customer);