JOIN operations combine rows from two or more tables based on a related column between them.

These tables and sample data are used in the examples below:

CREATE TABLE table_join_left
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR(20)
);

CREATE TABLE table_join_right
(
    id2 NUMBER(2,0)
,   desc2 VARCHAR(20)
);

INSERT INTO table_join_left (id1, desc1) VALUES (11, 'table_join_left_11');
INSERT INTO table_join_left (id1, desc1) VALUES (12, 'table_join_left_12');
INSERT INTO table_join_left (id1, desc1) VALUES (13, 'table_join_left_13');

INSERT INTO table_join_right (id2, desc2) VALUES (11, 'table_join_right_11');
INSERT INTO table_join_right (id2, desc2) VALUES (12, 'table_join_right_12');
INSERT INTO table_join_right (id2, desc2) VALUES (14, 'table_join_right_13');

COMMIT;

Understanding Join Mechanics:
A join operation involves two tables: a left table and a right table. The Oracle optimizer determines the most efficient execution strategy based on factors like table size, available indexes, and statistics.

Conceptually, a join operation consists of:

• An outer loop that processes each row of the left table.
  
• For each row in the left table, an inner loop examines rows in the right table to find matches based on the join condition.
  
• When the join condition is satisfied, the matching rows are combined into a single result row.
  

The conceptual join process between tables table_join_left and table_join_right can be visualized as:

for(ROW RowL : table_join_left) { // outer loop: RowL represents a row of the current iteration of the outer loop on table table_join_left
    for(ROW RowR : table_join_right) { // inner loop: RowR represents a row of the current iteration of the inner loop on table table_join_right
        // Join condition evaluation and result processing occurs here
    }
}

Note:
While nested loops provide an intuitive way to understand join operations, Oracle Database uses various join algorithms including hash joins, sort-merge joins, and nested loop joins. The optimizer selects the most efficient method based on table statistics, available indexes, and query characteristics.

An INNER JOIN returns only the rows that have matching values in both tables. It is the most commonly used join type and forms the foundation for understanding other join operations.

The keyword INNER is optional; writing just JOIN defaults to an INNER JOIN in Oracle SQL.

SELECT  *
FROM    table_join_left tj1
JOIN    table_join_right tj2
        ON tj2.id2 = tj1.id1

Query result:

ID1 DESC1              ID2 DESC2
--- ------------------ --- -------------------
11  table_join_left_11 11  table_join_right_11
12  table_join_left_12 12  table_join_right_12

How INNER JOIN Works:
The INNER JOIN operation consists of:

• An outer loop that iterates through each row of the left table (table_join_left).
  
• For each iteration of the outer loop, another nested loop iterates through the rows of the right table (table_join_right).
  
• For each iteration of the inner loop, a join condition is applied to the current row of the outer loop and the current row of the inner loop.
  
• If the condition is satisfied for both rows, they are merged into a single row, which is added to the join result.
  

The join between two tables table_join_left and table_join_right can be seen as follows:

for(ROW RowL : table_join_left) { // outer loop
    for(ROW RowR : table_join_right) { // inner loop
        if(CONDITION(RowL, RowR)) { // join condition
            // keep the rows: RowL and RowR
        }
    }
}

Note:
INNER JOINs are efficient when both tables have appropriate indexes on the join columns.

A LEFT OUTER JOIN returns all rows from the left table, along with matching rows from the right table. When no match exists, NULL values are returned for columns from the right table.

The keyword OUTER is optional in Oracle SQL.

SELECT  *
FROM    table_join_left tj1
LEFT JOIN   table_join_right tj2
            ON tj2.id2 = tj1.id1

Query result:

ID1    DESC1              ID2    DESC2
------ ------------------ ------ -------------------
11     table_join_left_11 11     table_join_right_11
12     table_join_left_12 12     table_join_right_12
13     table_join_left_13 (null) (null)

How LEFT OUTER JOIN Works:
The LEFT OUTER JOIN operation consists of:

• An outer loop that iterates through the rows of the left table (table_join_left).
  
• For each iteration of the outer loop, another nested loop iterates through the rows of the right table (table_join_right).
  
• For each iteration of the inner loop, a join condition is applied to the current row of the outer loop and the current row of the inner loop.
  
• If the condition is satisfied for both rows, they are merged into a single row, which is added to the join result.
  
• If the inner loop finishes, but the join condition was not satisfied between any rows of the right table and the current row of the outer loop, then the columns of a new row from the right table will be initialized to NULL. The current row of the outer loop and the new row from the right table will be merged into a single row, which is added to the join result.
  

The join between two tables table_join_left and table_join_right can be seen as follows:

for(ROW RowL : table_join_left) { // outer loop
    match = false;

    for(ROW RowR : table_join_right) { // inner loop
        if(CONDITION(RowL, RowR)) { // join condition
            // keep the rows: RowL and RowR
            match = true;
        }
    }

    if(match == false) { // no match
        // initialize a new empty row from table table_join_right: RowR_NULL
        // initialize the columns of the new row to NULL
        // keep the rows: RowL and RowR_NULL
    }
}

Note:
LEFT OUTER JOINs are particularly useful when you need to preserve all rows from a primary table while optionally including related data.

A RIGHT OUTER JOIN is the mirror image of a LEFT OUTER JOIN. It returns all rows from the right table, along with matching rows from the left table. When no match exists, NULL values are returned for columns from the left table.

The keyword OUTER is optional in Oracle SQL.

SELECT  *
FROM    table_join_left tj1
RIGHT JOIN  table_join_right tj2
            ON tj2.id2 = tj1.id1

Query result:

ID1    DESC1              ID2    DESC2
------ ------------------ ------ -------------------
11     table_join_left_11 11     table_join_right_11
12     table_join_left_12 12     table_join_right_12
(null) (null)             14     table_join_right_13

How RIGHT OUTER JOIN Works:
The RIGHT OUTER JOIN operation consists of:

• An outer loop that iterates through the rows of the right table (table_join_right).
  
• For each iteration of the outer loop, another nested loop iterates through the rows of the left table (table_join_left).
  
• For each iteration of the inner loop, a join condition is applied to the current row of the outer loop and the current row of the inner loop.
  
• If the condition is satisfied for both rows, they are merged into a single row, which is added to the join result.
  
• If the inner loop finishes, but the join condition was not satisfied between any rows of the left table and the current row of the outer loop, then the columns of a new row from the left table will be initialized to NULL. The current row of the outer loop and the new row from the left table will be merged into a single row, which is added to the join result.
  

The join between two tables table_join_left and table_join_right can be seen as follows:

for(ROW RowR : table_join_right) { // outer loop
    match = false;

    for(ROW RowL : table_join_left) { // inner loop
        if(CONDITION(RowL, RowR)) { // join condition
            // keep the rows: RowL and RowR
            match = true;
        }
    }

    if(match == false) { // no match
        // initialize a new empty row from table table_join_left: RowL_NULL
        // initialize the columns of the new row to NULL
        // keep the rows: RowL_NULL and RowR
    }
}

Note:
RIGHT OUTER JOINs are less commonly used than LEFT OUTER JOINs because most developers prefer to rewrite them as LEFT OUTER JOINs by switching the table order. This approach maintains consistency and improves query readability.

A FULL OUTER JOIN combines the results of both LEFT and RIGHT OUTER JOINs. It returns all rows from both tables, matching them where possible and filling unmatched rows with NULL values for the non-contributing table.

The keyword OUTER is optional in Oracle SQL.

SELECT  *
FROM    table_join_left tj1
FULL JOIN   table_join_right tj2
            ON tj2.id2 = tj1.id1

Query result:

ID1    DESC1              ID2    DESC2
------ ------------------ ------ -------------------
11     table_join_left_11 11     table_join_right_11
12     table_join_left_12 12     table_join_right_12
13     table_join_left_13 (null) (null)
(null) (null)             14     table_join_right_13

How FULL OUTER JOIN Works:
The FULL OUTER JOIN operation consists of:

• An outer loop that iterates through the rows of the left table (table_join_left).
  
• For each iteration of the outer loop, another nested loop iterates through the rows of the right table (table_join_right).
  
• For each iteration of the inner loop, a join condition is applied to the current row of the outer loop and the current row of the inner loop.
  
• If the condition is satisfied for both rows, they are merged into a single row, which is added to the join result.
  
• If the inner loop finishes, but the join condition was not satisfied between any rows of the right table and the current row of the outer loop, then the columns of a new row from the right table will be initialized to NULL. The current row of the outer loop and the new row from the right table will be merged into a single row, which is added to the join result.
  
• Additionally, for each row in the right table that was not matched with any row from the left table, the columns of a new row from the left table will be initialized to NULL. The new row from the left table and the current row of the right table will be merged into a single row, which is added to the join result.
  

The join between two tables table_join_left and table_join_right can be seen as follows:

for(ROW RowL : table_join_left) { // outer loop
    match = false;

    for(ROW RowR : table_join_right) { // inner loop
        if(CONDITION(RowL, RowR)) { // join condition
            // keep the rows: RowL and RowR
            match = true;
        }
    }

    if(match == false) { // no match
        // initialize a new empty row from table table_join_right: RowR_NULL
        // initialize the columns of the new row to NULL
        // keep the rows: RowL and RowR_NULL
    }
}

// Handle unmatched rows from table table_join_right
for(ROW RowR : table_join_right) { // loop through table table_join_right
    match = false;

    for(ROW RowL : table_join_left) { // check against table table_join_left
        if(CONDITION(RowL, RowR)) { // join condition
            match = true;
            break; // already processed in first loop
        }
    }

    if(match == false) { // no match
        // initialize a new empty row from table table_join_left: RowL_NULL
        // initialize the columns of the new row to NULL
        // keep the rows: RowL_NULL and RowR
    }
}

Note:
FULL OUTER JOINs are valuable for data reconciliation tasks, such as comparing datasets to identify missing rows in either table. However, they can be resource-intensive on large tables since they must process all rows from both tables. Consider using UNION ALL with separate LEFT and RIGHT OUTER JOINs for better performance in some scenarios.

A CROSS JOIN produces the Cartesian product of two tables, returning every possible combination of rows from both tables. Unlike other joins, CROSS JOIN does not use a join condition and should be used with caution due to the potentially large result sets it generates.

SELECT  *
FROM    table_join_left tj1
CROSS JOIN  table_join_right tj2

Query result:

ID1 DESC1              ID2 DESC2
--- ------------------ --- -------------------
11  table_join_left_11 11  table_join_right_11
11  table_join_left_11 12  table_join_right_12
11  table_join_left_11 14  table_join_right_13
12  table_join_left_12 11  table_join_right_11
12  table_join_left_12 12  table_join_right_12
12  table_join_left_12 14  table_join_right_13
13  table_join_left_13 11  table_join_right_11
13  table_join_left_13 12  table_join_right_12
13  table_join_left_13 14  table_join_right_13

How CROSS JOIN Works:
The CROSS JOIN operation consists of:

• An outer loop that iterates through every row of the left table (table_join_left).
  
• For each iteration of the outer loop, an inner loop processes every row of the right table (table_join_right).
  
• For each iteration of the inner loop, the current row of the outer loop and the current row of the inner loop will be merged into a single row, which is added to the join result.
  

The Cartesian product between two tables table_join_left and table_join_right can be seen as follows:

for(ROW RowL : table_join_left) { // outer loop
    for(ROW RowR : table_join_right) { // inner loop
        // keep the rows: RowL and RowR
    }
}

Note:
CROSS JOINs can quickly generate enormous result sets. For example, joining two tables with 1,000 rows each produces 1,000,000 result rows. Use CROSS JOINs only when you specifically need all possible combinations.