15.10 - Correlated Joins - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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 (see “Inclusion Merge Join Process” on page 467)
  • Exclusion merge join (see “Exclusion Merge Join” on page 463)
  • Inclusion product join (see “Inclusion Product Join Process” on page 467)
  • Exclusion product join (see “Exclusion Product Join” on page 465)
  • 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:

    There are six basic types of correlated join. Each type 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” on page 467) 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 two 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” on page 463.

    This join comes in two 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” on page 467.

  • Correlated exclusion product join
  • Correlated version of standard exclusion product join. See “Exclusion Product Join” on page 465.

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