Home » Oracle SQL » Oracle Self Join with Examples

Oracle Self Join with Examples

We will be learning the usage of Oracle SELF JOIN in this tutorial for joining a table with itself.

A SELF JOIN is used for joining a table with itself and serves the purpose of either comparing the values in one column from values in other column within the same table or to query hierarchical data.

We denote SELF JOIN by either using INNER JOIN or LEFT JOIN and use table alias for differentiating the same table that we mention two times in the query. Not using Table Alias will result in an error as we can’t reference the same table two times.

Syntax:

The syntax of Oracle SELF JOIN is as follows:

SELECT
    column_names
FROM
    T table_1 a
INNER JOIN T table_1  b ON
    join_condition;

Other than INNER JOIN we can also use LEFT JOIN to denote the SELF JOIN.

Here,

Table_1 – It’s the table on which we want to perform self join.

a and b- ‘a’ and ‘b’ are the table alias given to the same table.

Let’s see some examples to understand SELF JOIN.

Oracle SELF JOIN Examples

For the coming examples, we will be using Employees table from the HR schema

1. Oracle SELF JOIN Example Hierarchy

In this example, we use Oracle Self Join for comparing rows within the same table. We use the following example for finding manager of each employee.

SELECT 
e.first_name || '''s manager is ' 
||m.last_name || '.'
FROM employees e, employees m
WHERE e.manager_id = m.employee_id ;

Result:

Each employee in the table has a manager ID associated with it. We use SELF JOIN for referencing the same table twice in a same query by using table alias. The Join Condition matches the employees with their managers using the manager_id and employee_id columns.

This query shows us hierarchical data within the same table.

2. SELF JOIN for comparing rows within the same table

We use the following query for finding employees in the table having same first names.

SELECT
a.employee_id,
(a.first_name || ' ' || a.last_name) Employee1,
b.employee_id,
(b.first_name || ' ' || b.last_name) Employee2  
FROM
    employees a
INNER JOIN employees b ON
    a.first_name = b.first_name
WHERE a.employee_id <> b.employee_id
ORDER BY  
   a.first_name;

Result:

Oracle Self Join for comparing rows within the same table

The query returns all employees having same first names with their similar Employee ID’s.

We can use Self Join for doing a comparison of values within the same table.

In this tutorial, we learned how to use SELF JOIN for returning hierarchical data and for comparing values in a table against the values in the same table.

<< Previous
CROSS JOIN

Next >>
ORACLE HOME PAGE