JOIN

Q: How the Optimizer Executes Join Statements ?

The join clause combines the output from two row sources, such as tables or views, and returns one row source. The returned row source is the Data Set.

Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.

When multiple tables exist in the FROM clause, the optimizer must determine which join operation is most efficient for each pair. 


The optimizer must make the decision depending on the following interrelated terms in oracle:

1. Join Type        
2. Join Condition
3. Join Methods
4. Join Order

5. Access Path



1. Join Types

There are different Join types in oracle - 

• Inner join  - Equijoin, Nonequijoin
• Outer join  - Left, Right, Full 
• Semi join - IN, EXISTS 
• Anti join   - NOT IN, NOT EXISTS

2. Join Conditions

Usually, when you write a join query, you specify a condition that conveys a relationship between the tables specified in the FROM clause. This condition is referred to as the join condition.
 

The join condition specifies how the rows from one table will be combined with the rows of another table.
 

This join condition is usually applied to the foreign key columns of one table and the primary or unique key columns of another table.

Example - 1:

SELECT d.dept_id, d.name, l.regional_group
FROM department d
JOIN location l
ON (d.location_id = l.location_id);

Here, the ON clause specifies the join condition by which the location_id column of the department table is equated with the location_id column of the location table.
To perform this Inner Equijoin, Oracle picks up one combination of rows from the two tables, and checks to see whether the join condition is true. If the join condition is true, Oracle includes this combination of rows in the result set. This process is repeated for all combinations of rows from the two tables.

Example - 2:

SELECT employee_id, last_name, first_name
FROM   employees
LEFT OUTER JOIN departments
ON (employees.department_id=departments.departments_id);

Here, the ON clause specifies the join condition.
To perform this LEFT OUTER JOIN, oracle picks up all rows that satisfy the join condition and also returns some or all of those rows from left/driving table for which no rows from the other satisfy the join condition.

Note:
The columns specified in a join condition need not be specified in the SELECT list.



3. Join Order

To execute a statement that joins more than two tables, oracle database joins two tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.
 

For example - 
The database joins two tables, and then joins the result to a third table, and then joins this result to a fourth table, and so on.


4. Join Methods

To join each pair of row sources, Oracle Database must decide how to do it. 
The "how" is the join method. 

The possible join methods are -  

•  Nested Loop
•  Sort Merge
•  Hash Join

A Cartesian join requires one of the preceding join methods. 

Each join method has specific situations in which it is more suitable than the others.



5. Access Paths

As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.

For example
The optimizer might choose between a full table scan or an index scan.