This SQL tutorial focuses on the Oracle Self Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the JOIN statement in Oracle. To read additional posts regarding this subject, please use the following links:

  • Oracle Inner JOIN – Joining data items from tables, based on values common to both tables.
  • Oracle Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table
  • Oracle Self JOIN– Join a table to itself

Oracle Self JOIN

The Oracle Self Join allows joining a table to itself. To fully understand this concept, please consider the following example: the table in the illustration below contains information about employees and their managers. If you’ll take a closer look, you’ll notice this table has a certain hierarchy of employees and managers, where a certain employee may also be a manager (for example employee number 5 – Tim, is also the manager of David).

sql_self_join

sql_self_join_hierarchy

Since the table references data in itself, you can answer questions such as: what is the name of David’s manager? To find out, check what is the manager ID of David’s manager (5), and then check to which employee this number corresponds (Tim).

Employee’s manager ID = “Manager’s” employee ID.

On tables of this type you may sometimes want to display data that relies on this relation. You can use this relation to create a query that retrieves the employee’s name and the name of this employee’s manager.

SELECT emp.employee_id , emp.last_name , emp.manager_id ,
       mng.last_name
FROM   employees emp ,employees mng
WHERE  emp.manager_id = mng.employee_id

Because this operation joins the same table to itself, it actually involves referring the same table twice, each time under a different alias (once as “Employees” table, once as “Managers”).

The Oracle WHERE clause is used for defining the relation of the table to itself. In this case, the number of the employee’s manager (“Emp.Manager_ID”) is compared with the employee number of this manager (“Mng.Employee_ID”).