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.
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,
INSERT INTO [dbo].[Self_Join_Ex]
SELECT 1, ‘Ajay’, 2
SELECT 2, ‘Merry’, 3
SELECT 3, ‘Gary’, 4
SELECT * FROM [dbo].[Self_Join_Ex]
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]
Note: When we are using Self join, Aliasing for table names is mandatory. Otherwise we will get Ambiguity errors.