Same-Table Joins, Outer Joins, and Cartesian Product Joins

1. Same-Table Joins (Self-Joins)

A same-table join^[1] is an inner join in which a table is joined to itself. This technique is used when the rows of a single table contain a hierarchical or recursive relationship. A classic example is an Employee table that stores both employees and their supervisors in the same structure.
Consider the following EMPLOYEE table:

Because supervisors are also employees, the query must use two instances of the same table -one representing the employee, one representing the supervisor. The resulting same-table join is shown below:

2. Outer Joins

An outer join^[2] returns all rows from one table, even when no matching row exists in the other table. Inner joins return only matching pairs; outer joins return unmatched rows as well, filling missing values with NULL.
In the example below, the Customer table contains customers who may or may not have placed orders.

You use an outer join when the business rule requires showing all rows from one table, regardless of whether the related table contains matching rows. Common examples include:

• Show all customers and any orders they may have placed.
• Show all departments and any employees in each.
• Show all products and any sales activity.

3. Cartesian Product Joins

A Cartesian product^[3] (also called a cross join) occurs when no join condition is supplied. Every row from the first table is combined with every row from the second table. The result is usually unintended and massive, especially for large tables.
The Cartesian product of the Customer and Order tables looks like this:

Cartesian products are almost never useful for real-world reporting. In fact, they are one of the most common SQL mistakes made by beginners - typically caused by forgetting a join condition. Because the number of resulting rows is rows(tableA) × rows(tableB), the size of the result set grows explosively.

High-Level Overview of Oracle Joins

Create Equi Join