Exclusion Product Join Process
- For each left table row, read all right table rows from the beginning until one is found that can be joined with it.
- Produce the join result.
If no matching right table rows are found, return the left row.
Teradata Database 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:
SELECT name 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:
- 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.
- The single row found to satisfy this condition, that for department 600, is duplicated right away, without being spooled in the local AMP.
- The rows in the two spools undergo an exclusion product join on each AMP.
- 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 results spools are sent to the requesting application via a BYNET merge.
The following graphic illustrates this process: