Oracle Self Join

Summary: in this tutorial, you will learn how to join a table to itself using Oracle self join to query hierarchical data and compare the rows within the same table.

Introduction to Oracle Self Join

A self join is a join that joins a table with itself. A self join is useful for comparing rows within a table or querying hierarchical data.

A self join uses other joins such as inner join and left join. In addition, it uses the table alias to assign the table different names in the same query.

Note that referencing the same table more than once in a query without using table aliases cause an error.

The following illustrates how the table T is joined with itself:

SELECT
    column_list
FROM
    T t1
INNER JOIN T t2 ON
    join_predicate;Code language: SQL (Structured Query Language) (sql)

Note that besides the INNER JOIN, you can use the LEFT JOIN in the above statement.

Oracle Self Join example

Let’s look at some examples of using Oracle self join.

A) Using Oracle self join to query hierarchical data example

See the following employees table in the sample database.

The  employees table stores personal information such as id, name, job title. In addition, it has the manager_id column that stores the reporting lines between employees.

The President of the company, who does not report to anyone, has a NULL value in the manager_id column. Other employees, who have a manager, have a numeric value in the manager_id column, which indicates the id of the manager.

To retrieve the employee and manager data from the employees table, you use a self join as shown in the following statement:

SELECT
    (e.first_name || '  ' || e.last_name) employee,
    (m.first_name || '  ' || m.last_name) manager,
    e.job_title
FROM
    employees e
LEFT JOIN employees m ON
    m.employee_id = e.manager_id
ORDER BY
    manager;Code language: SQL (Structured Query Language) (sql)

The following picture shows the result:

This query references the employees table twice: one as e (for employee) and another as m (for manager). The join predicate matches employees and managers using the employee_id and  manager_id columns.

B) Using Oracle self join to compare rows within the same table example

The following statement finds all employees who have the same hire dates:

SELECT
   e1.hire_date,
  (e1.first_name || ' ' || e1.last_name) employee1,
  (e2.first_name || ' ' || e2.last_name) employee2  
FROM
    employees e1
INNER JOIN employees e2 ON
    e1.employee_id > e2.employee_id
    AND e1.hire_date = e2.hire_date
ORDER BY  
   e1.hire_date DESC,
   employee1, 
   employee2;Code language: SQL (Structured Query Language) (sql)

The  e1 and e2 are table aliases for the same  employees table.

In this tutorial, you have learned how to use an Oracle self join to query hierarchical data and compare rows within the same table.