Oracle Self Join

Last update on August 19 2022 21:51:21 (UTC/GMT +8 hours)

What is Self Join in Oracle?

A self join is a join in which a table is joined with itself.
To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition

Syntax:

SELECT a.column_name, b.column_name... 
FROM table1 a, table1 b 
WHERE a.common_filed = b.common_field;

Example: Oracle Self Join

The following SQL query uses a self join to return the name of each employee along with the name of the employee's manager.

Sample table: employees

SQL Code:

SQL> SELECT e1.last_name||' works for '||e2.last_name
  2     "Employees and Their Managers"
  3     FROM employees e1, employees e2
  4     WHERE e1.manager_id = e2.employee_id;

Sample Output:

Employees and Their Managers
----------------------------------
Smith works for Cambrault
Ozer works for Cambrault
Kumar works for Cambrault
Fox works for Cambrault
Bloom works for Cambrault
Bates works for Cambrault
Hunold works for De Haan
Vishney works for Errazuriz
Marvins works for Errazuriz
Lee works for Errazuriz
Greene works for Errazuriz

...

Self-Joins Using the ON Clause

The ON clause can be used to join columns that have different names, within the same table or in a different table. In following SQL query we have used employee_id and manager_id as joining columns. The query returs the name of each employee’s manager.

Syntax:

SELECT a.column_name, b.column_name... 
FROM table1 a JOIN table1 b 
ON (a.common_filed = b.common_field);

SQL Code:

SQL> SELECT e1.last_name "Worker", e2.last_name "Manager"
  2  FROM employees e1 JOIN employees e2
  3  ON  (e1.manager_id = e2.employee_id);

Sample Output:

Worker                    Manager
------------------------- -----------
Smith                     Cambrault
Ozer                      Cambrault
Kumar                     Cambrault
Fox                       Cambrault
Bloom                     Cambrault
Bates                     Cambrault
Hunold                    De Haan
Vishney                   Errazuriz
Marvins                   Errazuriz
Lee                       Errazuriz
Greene                    Errazuriz
Banda                     Errazuriz
Ande                      Errazuriz
Sarchand                  Fripp
Olson                     Fripp
Marlow                    Fripp
Dellinger                 Fripp
Cabrio                    Fripp
Bull                      Fripp
Bissot                    Fripp
Atkinson                  Fripp
...

Note: The parenthesis around the joined columns ais optional.

Outputs of the said SQL statement shown here is taken by using Oracle Database 11g Express Edition.