Self Join:

Let’s explain the need for a self join with an example,

 Employee Table:

I want to retrieve employee’s data for country India.

We can retrieve data using classic sub query use but this is not efficient solution.

SELECT * FROM [Employee] WHERE COUNTRY IN (SELECT Country FROM [Employee] WHERE EmployeeName='Nagnath')

What if I want to check manager name for employee?

Answer: Efficient solution is self join   

Definition: A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table. And that is exactly why it is called a self join – because of the fact that it’s just the same table being joined to another copy of itself rather than being joined with a different table.

Note: for self join use aliases otherwise the column names would be ambiguous.

Self join example:

As with any join there must be condition upon which self join is performed (i.e. inner join, outer join)

SELECT e1.*
FROM employee e1, employee e2
WHERE e1.Country = e2.Country
AND e2.EmployeeName='Nagnath';

Find managers for employee we can do this by self join:

SELECT e1.EmployeeName,e1.Country,e1.PrimaryLanguage,e1.Gender ,e2.EmployeeName as Manager
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.ID