16.10 - Correlated Joins - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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. Geospatial column terms are not permitted for outer join conditions.

  • 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.