Self Join in SQL


Self Join in SQL

The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.

Below is the Employee table which has EmployeeId and ManagerId.

Now, if we want to see the manager of each employee, we will have to create a Self Join as follows n thr Employee table

					SELECT (E.FirstName +' '+ E.LastName) as EMPLOYEE,
       (M.FirstName +' '+ M.LastName) as MANAGER FROM EMPLOYEE E
M.EmployeeId = E.ManagerId

In the above query we have created two alias of the Employee table “Employee and Manager”, which are acting as two tables for the same Employee table, and performed a join between the  EmployeeId and ManagerId, that will give the below result, which consist of Employee name along with its Manager name

Please comment and share if you like this post and tell us about how we can enhance our posts in dotnetcoaching. Thanks.