What is SELF JOIN in SQL Server ?

types of join in sql server - self join

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.

self join table example in sql server

Syntax of Self Join

SELECT < list of columns >
FROM table1 AS t1 , table1 AS t2
WHERE t1.column1Value = t2.Column1Value

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


Select the employee and there respective manager using self join.


Example

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

Output

selef join example in sql server


SELF JOIN with multiple where condition.

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


Example

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

selef join example in sql server