Correlated Joins | Join Planning/Optimization | Teradata Vantage - 17.10 - Correlated Joins - 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)

About Correlated Joins

Correlated join constitutes a class of join methods developed to process correlated subqueries. Some types of Correlated join are extensions of the following more general join types:
  • Inclusion merge join
  • Exclusion merge join
  • Inclusion product join
  • Exclusion product join

For each of these types the right table is a collection of groups and a left row can be returned once for each group.

Other members of the correlated join family are unique types.

The following graphic illustrates the generic correlated join process:


Generic correlated join process

Correlated Join Types

Each basic type of correlated join has an inner join version and an outer join version.
  • Correlated inclusion merge join
    Similar to the simple inclusion merge join (see Inclusion Merge Join Process) except for the handling of groups and the following additional considerations:
    • Right table rows are sorted by row hash within each group.
    • Each left table row must be merge joined with each group of the right table.

      This join comes in the following forms:

    • Correlated inclusion fast path merge join
    • Correlated inclusion slow path merge join
  • Correlated exclusion merge join
    Correlated version of standard exclusion merge join. See Exclusion Merge Join. This join comes in the following forms:
    • Correlated exclusion fast path merge join
    • Correlated exclusion slow path merge join
  • Correlated inclusion product join

    Correlated version of standard inclusion product join. See Inclusion Product Join Process.

  • Correlated exclusion product join

    Correlated version of standard exclusion product join. See Exclusion Product Join.

  • EXISTS join

    If a right table row exists, then return all left table rows that satisfy the condition.

  • NOT EXISTS join

    If the right table has no rows, then return all left table rows that do not satisfy the condition.