SELF JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

As the name Suggests SELF JOIN is joining a table with it self.

Often a table needs to be self joined to fetch the required results. In such cases SELF JOIN will be used.

SELF JOIN is not a keyword or Command, Its just the name of the JOIN.

There is no specific syntax for SELF JOIN. SELF JOIN can be done using either of the following joins:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

Example:

Consider a table which stored Employee details of an organisation.

Data in the EMPLOYEE table is given below:




















Employee table contains Employee Id, Employee Name, Salary and Employee's Manager ID.

Manager is also an Employee. So, Manager details will also be stored in the same table.

In order to find out the Name of the manager for an EMPLOYEE ;   EMPLOYEE  table has to be joined with it self.

Query for fetching the Employee's Manager Name is:

SELECT A.EMP_ID, A.EMP_NAME,A.EMP_SALARY,B.EMP_NAME
FROM EMPLOYEE A
LEFT JOIN EMPLOYEE B
ON A.MANAGER_ID=B.EMP_ID



Result Set for the above Query is:













In the above Query Left side table and Right side table both are one and the same. That is here a table is self joined to the same table.


To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

This entry was posted in , . Bookmark the permalink.

Leave a reply