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.
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;
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.
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;
The following picture shows the result:
This query references to the
employees table twice: one as
e (for employee) and another as
m (for manager). The join predicate matches employees and managers using the
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;
e2 are table aliases for the same
In this tutorial, you have learned how to use an Oracle self join to query hierarchical data and compare rows within the same table.