SELF JOIN in SQL SERVER with example

A self-join is a query in which a table is joined (compared) to itself.   So basically this join is always performed on single table as opposed to other joins that need more than one table.  Self-joins are used to compare values in a column with other values in the same column in the same table.
To write the self join query we use a table twice in FROM clause and assign different alias to each instance of table  because tables participating in self join have same name.

Syntax for SELF JOIN :

SELECT a.column_name, b.column_name...

FROM Table1 a, Table1 b

WHERE a.common_field = b.common_field;

working of SELF JOIN

First of all create a table for storing Employee details like Name, Address, City and managerId using the Script below.

CREATE TABLE [dbo].[EmpDetails]

(

                [EmpId] [int] IDENTITY(1,1) NOT NULL,

                [Name] [varchar](50) NULL,

                [Address] [varchar](200) NULL,

                [City] [varchar](50) NULL,

                [ManagerId] [int] NULL

)

Insert Some data in this table:
This table will look like as show in image below:

Suppose our requirement is to find the employee name and their manager names. We need to write the following query:

SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName

FROM EmDetails e1

INNER JOIN EmDetails e2

ON e1.ManagerId = e2.EmpId

ORDER BY e1.Name

Output will be like as shown in image below: