Basics of SQL - partIV(Self Join)

Self Join is probably one of the most difficult concepts in database because we often get confused
about the appropriate situation which demands its use.

So its worth discussing.

Self join is essentially joining the same table with itself.

Now the question is why should anybody joins a table with itself.

Well as the business complexities increase, having a situation where the rows of a resultset would be consisting of information from more than one rows of itself becomes very much common. This becomes a classic self join scenario.

Now lets talk about the employee, manager example which probably all the institutes teach whoever teaches SQL . This is one of my favourite examples for self join as well.

Lets say we have a table called employee.

Now in this case if the business expects us to show the employee name and manager name in the same row, it becomes difficult or practically impossible without the use of a self join. But self join makes it very simple.

The query which is used for displaying the employee name and manager name in the same row is

Select a.name as Employee_Name, b.name as Manager_Name
from Employee a inner join Employee b
on a.mgrid=b.employeeid

Is not it very easy..