Using Self Join on a table in Sql server | SQL Self Join Example

Hello in this post i will show how to use self join in Sql Server.

1. Suppose i have a table which has the data for both Employee and the Employee Department in a single table.
And if we are told to write a query to get employee name and there respective department name from that table, then in that case we will use self join. i.e join with the same table.

Example: 

Suppose we have this below table which has data for both Employee and the Employee Department.

Sql Script for this table:

CREATE TABLE EMPLOYEEDATA (
 EMPID INT
 ,EMPNAME VARCHAR(500)
 ,EMPDEPTID INT
 ,DEPTID INT
 ,DEPTNAME VARCHAR(500)
 )
GO

INSERT INTO EMPLOYEEDATA (EMPID,EMPNAME,EMPDEPTID,DEPTID,DEPTNAME)
VALUES (1,'JASON',3,1,'ADMIN')

INSERT INTO EMPLOYEEDATA (EMPID,EMPNAME,EMPDEPTID)

VALUES (2,'LILI',2)

INSERT INTO EMPLOYEEDATA (EMPID,EMPNAME,EMPDEPTID,DEPTID,DEPTNAME)
VALUES (3,'MARIA',4,2,'VOICESUPPORT')

INSERT INTO EMPLOYEEDATA (EMPID,EMPNAME,EMPDEPTID,DEPTID,DEPTNAME)
VALUES (4,'VINOD',3,3,'MANAGER')

INSERT INTO EMPLOYEEDATA (DEPTID,DEPTNAME)
VALUES (4,'ACCOUNTS')

INSERT INTO EMPLOYEEDATA (EMPID,EMPNAME,EMPDEPTID)
VALUES (5,'RAMESH',4)

GO

SELECT * FROM EMPLOYEEDATA

Now, writing the self join query where we just want to show  Emp Name along with Employee Dept Name in the query output.

Below is the Self Join Query to get the Output as shown in the above image:

SELECT E1.EMPNAME,E2.DEPTNAME

FROM EMPLOYEEDATA E1,EMPLOYEEDATA E2

WHERE E1.EMPDEPTID = E2.DEPTID