Correlated join is a class of join methods that process correlated subqueries. A correlated join may be an extension of one the following 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.