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 4 – Anne, is also the manager of George).
Since the table references data in itself, you can answer questions such as: what is the name of George’s manager? To find out, check what is the manager ID of George (3), and then check to which employee this number corresponds (Anne).
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”).
