A self join in SQL Server is a type of join where a table is joined to itself. This is useful when a table has a relationship with itself, such as when a table has a manager-employee relationship, where each employee can also be a manager of other employees.
The basic syntax for a self join in SQL Server is as follows:
SELECT column_name1, column_name2
FROM table_name1
JOIN table_name1
ON table_name1.column_name = table_name1.column_name
We first create table employees and insert some data
CREATE TABLE [dbo].[employees](
[emp_id] [int] NOT NULL,
[emp_name] [varchar](50) NOT NULL,
[salary] [int] NOT NULL,
[manager_id] [int] NULL
)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (1, N'Rajesh', 70000, NULL)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (2, N'Manish', 20000, 1)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (3, N'Manoj', 25000, 1)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (4, N'Rishi', 30000, 2)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (5, N'Salman', 50000, 1)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (6, N'Mukul', 45000, 5)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (7, N'Mahesh', 10000, 2)
INSERT [dbo].[employees] ([emp_id], [emp_name], [salary], [manager_id]) VALUES (8, N'Uma', 15000, 3)
Now our table looking just like see below table
Now we want to employee name with his manager id so we make a query se below example
select T1.emp_name,T1.salary,T2.emp_name as Manager
from employees as T1
Left join employees as T2 on T1.manager_id=T2.emp_id
Now Output Looks like
