When and why should we use a self join in SQL?

A Self Join is simply a regular join operation where a table is joined with itself. This allows us to compare rows within the same table, which is particularly useful when working with hierarchical data or when comparing related rows from a single table.

For Example, Consider the Following Employee Table

Employee Table

EmployeeId	EmployeeName	ManagerId
1	ABC	null
2	MNO	1
3	XYZ	1

 

Now, we need to perform self join on the table we created in order to retrieve the list of employees and their corresponding managers name and for that we will write the Query as

SELECT e.EmployeeName AS employee,
m.EmployeeName AS manager FROM 
EmployeeTable e JOIN EmployeeTable m 
ON e.ManagerId = m.EmployeeId;

You can use self join for

• Hierarchical Data: Self joins are particularly useful when working with hierarchical data such as organizational structures, where each employee has a manager.
• Finding Relationships: Self joins can be used to find relationships within the same table, such as identifying employees with similar attributes or roles.
• Data Comparison: Self joins allow comparing records within the same table based on specific conditions, like comparing sales figures of employees working in the same department.