Table Join

Nested Loop Join

The most distinguishing character of Nested Loop Join is that the outer query (driving query) determines the data volume that need to be processed. The Nested Loop Join performs well when the data volume is small and there are proper indexes on the join columns. The most outstanding disadvantage of the Nested Loop join is that it may cause too many random table accesses.

Sort Merge Join

Compared with Nested Loop Join, the SORT MERGE JOIN will not introduce much random table accesses. And there is no “driving table” in SORT MERGE JOIN.
If most of the join conditions are ‘LIKE’, ‘BETWEEN’, ‘>’, >=’, ‘<’, ‘<=’ instead of ‘=’, the SORT MERGE JOIN is better than Nested Loop Join.

Hash Join

Hash join is to use the hash function for the table join. And Hash Join can be only used when the join operator is “=”.

Semi Join

Semi join happens when there is sub query in the SQL statement. The join between “main query” and “sub query” is called semi join. Since the “sub query” is subject to “main query”, if the relationship between the main query and sub query is “M:1”, then the join between the main query and sub query is the same as the general table join, otherwise the sub query will be transformed into the “1” to make sure the final result is compatible with the main query.
The sub query can be executed earlier than the main query (acts as data provider) or later than the main query (acts as the data filter). In the first case, if the sub query is the “M” side, one operation named “SORT(UNIQUE)” will be involved to transform the sub query to the “1” side. In the second case, the sub query will be aborted when the first matching record in found.
Please note that the IN-list sub query will not necessarily be executed before the main query.

Cartesian Join

Cartesian join means there is no join condition between the two tables. Generally, the Cartesian join is executed as “Sort Merge Join”.
The typical sort merge join execution plan is as below…

MERGE JOIN (CATESIAN) TABLE ACCESS (FULL) OF 'emp' BUFFER (SORT) TABLE ACCESS (FULL) OF 'copy_t'

Outer Join

• Nested Loop Outer Join
• Hash Outer Join
  If the outer join query has (inline) view, the view merging will not be executed. Instead, the (inline) view must be executed separately before the table outer joins.
  If the inner table has some query condition, the outer join needs more caution.
  For example,

SELECT last_name, nvl(sum(ord_amt), 0) FROM customers c, order o WHERE c.cust_id = o.cust_id(+) AND c.credit_limit > 1000 AND o.ord_type IN ('01', '03') ------ query condition on the inner table GROUP BY last_name;

Please note the query condition “o.ord_type IN (‘01’, ‘03’)” will be used as the data filter which is executed after the outer join which leads to the wrong results.
To resolve this issue, we need to turn to “inline view” for help as the inline view will be executed first in the outer join.

SELECT last_name, nvl(sum(ord_amt), 0) FROM customers c, (SELECT cust_id, ord_amt FROM orders WHERE ord_type IN ('01', '03')) o WHERE c.cust_id = o.cust_id(+) AND c.credit_limit > 1000 GROUP BY last_name;

Another better solution is to use ANSI SQL…

SELECT c.last_name, nvl(sum(o.ord_amt), 0) FROM customers c LEFT OUTER JOIN orders o ON (c.cust_id = o.cust_id AND o.ord_type IN ('01', '03')) WHERE c.credit_limit > 1000 GROUP BY c.last_name;

• Sort Merge Outer Join
• Full Outer Join

Index Join

Index Join means if the table in the query has more than one index on the columns then use hash join to join these indexes together to get the final result. This means no need to query the table via the index, just retrieve the data using index join operation.