Let’s explain the need for a
self join with an example,
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
What if I want to check manager name for employee?
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)
e1, employee e2
WHERE e1.Country = e2.Country
Find managers for
employee we can do this by self join:
SELECT e1.EmployeeName,e1.Country,e1.PrimaryLanguage,e1.Gender ,e2.EmployeeName as
INNER JOIN Employee e2
ON e1.ManagerID = e2.ID