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.
Syntax of Self Join
Note : We not use JOIN and ON keyword in SELF JOIN.
Select the employee and there respective manager using self join.
SELF JOIN with multiple where condition.
Consider same above result set just find out the employee who belong from Pune city.