SQL Server - SELF JOIN

What is SELF JOIN in SQL Server ?

SELF JOIN the name itself indicate that apply join same table.
This is conceptual join because SQL Server not provide SELF keyword like INNER, LEFT, RIGHT and CROSS. While joining on same table we tow table we not use JOIN keyword.

The real time example of SELF JOIN is employee table where one employee can be a manager of another employee.
Second example is when we create dynamic menu that one menu can have multiple sub menus and again sub menu can have multiple sub menus.

Consider the below result set of employee table where one employee can be a manager of another employee. In below result set Vikas is manager of Poonam and Rupesh, and again Poonam is manager of Ganehs etc.

Note : We not use JOIN and ON keyword in SELF JOIN.

Select the employee and there respective manager using self join.

Example

Copy

SELECT e2.Id,
   e2.EmpName AS Employee,
   e1.EmpName AS Manager
FROM tblEmployee e1 , tblEmployee e2
WHERE e1.Id = e2.ManagerId

Output

SELF JOIN with multiple where condition.

Consider same above result set just find out the employee who belong from Pune city.

Example

Copy

SELECT e2.Id,
   e2.EmpName AS Employee,
   e1.EmpName AS Manager
FROM tblEmployee e1 , tblEmployee e2
WHERE e1.Id = e2.ManagerId AND e1.EmpCity = 'Pune'

Output