SQL using Self Join to retrieve data. SELF JOIN is a Join when the table is joined to itself.
Below is shown the "EmployeeInfo" table:
| 1 |
Georg Pipps |
NULL |
13000.00 |
CEO |
| 2 |
Rene Phillips |
1 |
7000.00 |
Sales Head |
| 3 |
Elizabeth Brown |
1 |
6500.00 |
HR Manager |
| 4 |
Hari Kumar |
1 |
6500.00 |
Tech Head |
| 5 |
Howard Snyder |
2 |
4500.00 |
Sales Officer |
| 6 |
Patricio Simpson |
2 |
4500.00 |
Sales Officer |
| 7 |
Yang Wang |
3 |
4000.00 |
HR |
| 8 |
Thomas Hardy |
3 |
4000.00 |
HR |
| 9 |
Antonio Moreno |
4 |
4700.00 |
Programmer |
| 10 |
Martine Rancé |
4 |
4700.00 |
Programmer |
SQL using SELF JOIN
In the "EmployeeInfo" table, EmployeeID is the Primary Key and it is used again as the ManagerID in the Same table again.
Since Managers are also Employees, they are maintained in the same table itself.
SQL Query to Retrieve Employee Name and Manager Name using SELF JOIN by SQL SUBQUERY.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select E1.EmployeeID, E1.EmployeeName, E1.ManagerID,
(Select EmployeeName from EmployeeInfo where EmployeeInfo.EmployeeID = E1.ManagerID) as ManagerName,
E1.Salary
From EmployeeInfo E1;
Note: E1.ManagerID is passed as the argument for the Subquery. It is self join since "EmployeeInfo" table is used in Subquery also.