SQL Self Join

Last update on April 13 2024 10:08:03 (UTC/GMT +8 hours)

What is Self Join in SQL?

A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :

SELECT a.column_name, b.column_name... 
FROM table1 a, table1 b 
WHERE a.common_filed = b.common_field;

For this tutorial we have used a table EMPLOYEE, that has one-to-many relationship.

Code to create the table EMPLOYEE

SQL Code:

CREATE TABLE employee(emp_id varchar(5) NOT NULL,
emp_name varchar(20) NULL,
dt_of_join date NULL,
emp_supv varchar(5) NULL,
CONSTRAINT emp_id PRIMARY KEY(emp_id) ,
CONSTRAINT emp_supv FOREIGN KEY(emp_supv) 
REFERENCESemployee(emp_id));

The structure of the table

In the EMPLOYEE table displayed above, emp_id is the primary key. emp_supv is the foreign key (this is the supervisor’s employee id).

If we want a list of employees and the names of their supervisors, we’ll have to JOIN the EMPLOYEE table to itself to get this list.

Unary relationship to employee

How the employees are related to themselves:

An employee may report to another employee (supervisor).
An employee may supervise himself (i.e. zero) to many employees (subordinates).

We have the following data into the table EMPLOYEE.

The above data shows:

Unnath Nayar's supervisor is Vijes Setthi
Anant Kumar and Vinod Rathor can also report to Vijes Setthi.
Rakesh Patel and Mukesh Singh are under supervison of Unnith Nayar.

Example of SQL SELF JOIN

In the following example, we will use the table EMPLOYEE twice and in order to do this we will use the alias of the table.

To get the list of employees and their supervisor the following SQL statement has used:

SQL Code:


-- Selecting specific columns: 'emp_id' and 'emp_name' from table 'a' (employee), and 'emp_id' and 'emp_name' from table 'b' (also employee)
SELECT a.emp_id AS "Emp_ID", a.emp_name AS "Employee Name", b.emp_id AS "Supervisor ID", b.emp_name AS "Supervisor Name"
-- Performing a self-join on the 'employee' table, aliasing the first instance as 'a' and the second instance as 'b'
FROM employee a, employee b
-- Defining the join condition where 'a.emp_supv' (employee's supervisor ID) equals 'b.emp_id' (supervisor's ID)
WHERE a.emp_supv = b.emp_id;

Explanation:

This SQL query performs a self-join on the 'employee' table to retrieve information about employees and their supervisors.

It selects specific columns from the table, aliasing the table instances as 'a' and 'b' for clarity.

The columns selected include 'emp_id' and 'emp_name' from both instances, representing both employees and their supervisors.

The WHERE clause defines the join condition, specifying that 'a.emp_supv' (employee's supervisor ID) must match 'b.emp_id' (supervisor's ID). This establishes the relationship between employees and their supervisors.

By performing this self-join, the query retrieves information about each employee and their corresponding supervisor.

This query is useful for analyzing hierarchical relationships within the organization, such as identifying supervisors for each employee.

Output:

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.