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.