A self-join is a join between multiple instances of the same table. With self-joins it is mandatory to alias the instances of the tables differently.

Whenever you have a parent-child relationship within a table you have an opportunity to use a self-join. For example, suppose you had a table of employees. In that table there is a column that contains that employee’s manager id.

Let’s have a look at a few columns of the Employees table used in Itzik Ben-Gan’s book in his book T-SQL Querying published by Microsoft Press in 2015.

Here is our query.

select E.firstname + ' ' + E.lastname as emp, M.firstname + ' ' + M.lastname as mgr
from HR.Employees as E
left outer join HR.Employees as M
on E.mgrid = M.empid 

Here are the results.

In a non-production server, you could use the code below to create the original table at the top of this post. I changed some of the names, however.

/****** Object:  Table [dbo].[OrgChart]    Script Date: 1/8/2023 12:30:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrgChart](
	[empid] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [nvarchar](10) NOT NULL,
	[title] [nvarchar](30) NOT NULL,
	[mgrid] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[OrgChart] ON 

INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (1, N'Sally', N'CEO', NULL)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (2, N'Donald', N'Vice President, Sales', 1)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (3, N'Jasmine', N'Sales Manager', 2)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (4, N'Bart', N'Sales Representative', 3)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (5, N'Hans', N'Sales Manager', 2)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (6, N'Peter', N'Sales Representative', 5)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (7, N'Ramone', N'Sales Representative', 5)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (8, N'Marcy', N'Sales Representative', 3)
INSERT [dbo].[OrgChart] ([empid], [firstname], [title], [mgrid]) VALUES (9, N'Pat', N'Sales Representative', 5)
SET IDENTITY_INSERT [dbo].[OrgChart] OFF
GO