What is Self join and why do we need it?

There is no special type of join called Self join in SQL. The Self join is a normal join which is used to join one table to itself. This kind of join is used when we have Unary relationship (Table refers to itself) in a table. Self join can be inner join or outer join.

Example:

We have an Employee table of a company ABC with the fields called Emp_ID, Emp_Name and Boss_ID.

Suppose we got the requirement to fetch the Employee Names of company and also the respective Boss names, we use Self join. 

—————————————————-
CREATE TABLE [dbo].[Self_Join_Ex]
(
[Emp_ID] int PRIMARY KEY,
[Emp_Name] Varchar(50),
[Boss_ID] int
)
GO
—————————————————-
INSERT INTO [dbo].[Self_Join_Ex]
SELECT 1, ‘Ajay’, 2
UNION ALL
SELECT 2, ‘Merry’, 3
UNION ALL
SELECT 3, ‘Gary’, 4
GO
—————————————————-
SELECT * FROM [dbo].[Self_Join_Ex]
GO
—————————————————-
SELECT [Emp].[Emp_Name] AS [Emp]
,[Boss].[Emp_Name] AS [Boss]
FROM [dbo].[Self_Join_Ex] AS [Emp]
JOIN [dbo].[Self_Join_Ex] AS [Boss]
ON [Emp].[Boss_ID] = [Boss].[Emp_ID]
GO
—————————————————-

Output:

Self-Join

Note: When we are using Self join, Aliasing for table names is mandatory. Otherwise we will get Ambiguity errors. 

Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *