Understanding Self Join Briefly with Example

Self join is easiest to understand. We can see how its working with one good example clearly. Consider the below table we have called Employee which have two columns name and location.

Employee Table

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

Now you have to write the query for the below question.

1. How you find out which employees are coming from same location as name have Millar.

After seeing the question you will write the query immediately like below.

select name
from Employee
Where location in (select location from employee where name = "Millar") 

Fine. It will work perfectly. But using sub query is not a good practice. So in this place SELF JOIN can play its role perfectly. 

What is SELF JOIN ? 

             Self join is nothing but, when a table is joined itself called self join. So it will take two copies to join the table itself. To work with self join we must use aliases which will allow you to write a query with good efficient way. The reason here, why we are using aliases means, we should differentiate two copies of that table. Right ?  Yes. 

Any join(left, right, outer etc.,) will work with condition only. So self join also having condition to perform self join operation. Now throw the sub query from your mind and bring your mind into Self Join. Already we have discussed , two copies of table it have. Just call those copies as e1, e2.

 Table  -    e1

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

 Table  -    e2

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

As we discussed earlier, two tables was created like e1 and e2. After writing query Self join query is like below. 

SELECT e1.Name
FROM employee e1, employee e2
WHERE e1.location = e2.location
AND e2.Name="Millar";

Lets analyze the query , the condition WHERE e1.location = e2.location will give results (Rows) of location. Again we must add another condition which should be e2.Name="Millar". Based on the name we should filter , so that we added this condition. But you may get one doubt here, why we want to add only e2.Name ?.   Since, we need to return only the rows name as Millar. If you will add condition for both side of e1 and e2, it will return both side of table rows.

So you will find below finally,

e1.Name	e1.Location	e2.Name	e2.Location
Millar	Chennai	Millar	Chennai
Stalin	Chennai	Stalin	Chennai