Self Join – How to Write Self Join Queries Easily

Self Join: By definition, a self join query is a query in which the table is joined to itself. Self joins are written in cases where there is parent child relationships in the data.

Let’s take the classic example of employees table. We have to write a self join query to find out the immediate manager of each employee. Sample data of employees table is shown below:

TableName: empTable
EMPLOYEEID  NAME     MANAGERID
------------------------------
101         Mary     102
102         Ravi     NULL
103         Raj      102
104         Pete     103
105         Prasad   103
106         Ben      103

The self join query to retrieve the manager of each employee.

select EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME 
from  empTable EMP, empTable MANAGER
where EMP.manager_id = MANAGER.employee_id

Writing Self Join Query Easily

It is always difficult for new SQL developers to write a self join query. Even the experienced ones struggle a bit while writing the self join query. A Self Join query is as easy as writing a normal join between two tables. Before writing the self join query, take a look at the relation between the employee and manager in the following image. The manager ID column in the employees table refers to the employee ID column.

EMPLOYEEID	NAME	MANAGERID
101	Mary	102
102	Ravi	Null
103	Raj	102
104	Pete	103
105	Prasad	103
106	Ben	103

Let’s imagine that there are two employee tables. Call the first one as EMP and the second one as MANAGER as show in the below image. Draw the relationship between these two tables.

Now the self join query becomes joining the data between two tables.

First, write a query to join the above two tables as

select EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME
from  EMP, MANAGER
where EMP.manager_id = MANAGER.employee_id

Now modify this query to self join as shown below:

select EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME
from  empTable EMP, empTable MANAGER
where EMP.manager_id = MANAGER.employee_id

To conclude, self join queries are very easy to write. One has to view the same table as two different tables and draw the relationship between these two tables to write a join query.