Join-Method Icons - Visual Explain

Icon

Join Step

Description

Correlated Exclusion Merge join

A correlated version of the Exclusion Merge Join. A subquery is correlated when it references columns of outer tables in the enclosing (outer) query.

Correlated Exclusion Product join

A correlated version of the Exclusion Product Join. A subquery is correlated when it references columns of outer tables in the enclosing (outer) query.

Correlated Inclusion Merge join

A correlated version of the Inclusion Merge Join. A subquery is correlated when it references columns of outer tables in the enclosing (outer) query.

Correlated Inclusion Product join

A correlated version of Inclusion Product Join. A subquery is correlated when it references columns of outer tables in the enclosing (outer) query.

Dynamic Hash join

An equality join between a small table and a large table on nonprimary index columns without placing the large table into a spool file. For Dynamic Hash join to be used, the left table must be small enough to fit in a single partition.

Exclusion Hash Join

Exclusion Hash Join returns the outer row when there is no matching inner row. The cost of Exclusion Hash Join is similar to Inner Hash Join with the exception that Exclusion Hash Join returns only the outer row when there is no matching inner row.

Exclusion Merge join

A merge join where only the rows that do not satisfy (are NOT IN) any condition specified in the request are joined.

Exclusion Product join

A Product Join where only the rows that do not satisfy (are NOT IN) any condition specified in the request are joined.

Exists join

A join that returns all the left table rows that satisfy a condition, if the right table is non-empty.

Full Outer Merge join

A join in which full outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the tables that were not returned in the result of the inner join, extending these rows with null values.

Full Outer Product join

A join in which full outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the tables that were not returned in the result of the inner join, extending these rows with null values.

Hash join

A hash join is applicable only to equijoins. This join eliminates the need to sort the tables being joined before performing the join operation.

Hash Star join

A join in which two or more small relations are joined to large a relation.

Inclusion Hash Join

Inclusion Hash Join is similar to Inner Hash Join with the exception of the cost of building a row and the probe cost. With Inclusion Hash Join, when there is a match, only the outer row is returned where the Inner Hash Join returns the join of the inner and outer row.

Inclusion Merge join

A join in which only the rows that satisfy (are IN) any condition specified in the request are joined.

Inclusion Product join

A join in which only the rows that satisfy (are IN) any condition specified in the request are joined.

Inner Merge join

A join that retrieves rows from two tables, then puts the rows onto a common AMP, based on the row hash of the columns involved.

Inner Product join

A product join that compares every qualifying row from one table to every qualifying row from another table. The number of comparisons needed is the product of the number of qualifying rows in the two tables.

Intersect join

A join in which the INTERSECT ALL clause is used and where the rows returned are common to both tables, including the duplicate values.

Left Outer Merge join

A join in which left‑outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the left join table that were not returned in the result of the inner join, extending these rows with null values.

Left Outer Product join

A join in which left outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the left join table that were not returned in the result of the inner join, extending these rows with null values.

Minus/Except join

A join that occurs when the MINUS ALL clause is used. Returns the rows that are present only in the left and not in the right table, including duplicate values.

Nested join

A join in which a WHERE condition specifies a constant value for a unique index of one table. The conditions can also match some column of that single row to the primary or secondary index of the second table.

Not Exists Join join

A join that returns all the left table rows that do not satisfy a condition, if the right table has no rows.

Right Outer Merge join

A join in which right outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the right join table that were not returned in the result of the inner join, extending these rows with null values.

Right Outer Product join

A join in which right outer joins of two or more tables perform an inner join of those tables according to a join condition. In addition, rows are returned from the right join table that were not returned in the result of the inner join, extending these rows with null values.

Row ID join

A join in which the condition specified in the query must match a column in the first table to a NUSI or USI of the second table. A subset of the NUSI or USI values is qualified, and a nested join between the two tables retrieves the row IDs from the second table.