Correlated Joins | Join Planning/Optimization | Teradata Vantage - Correlated Joins - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
uqf1592445067244.ditamap
dita:ditavalPath
uqf1592445067244.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantageā„¢

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:



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.