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.
SQL Query to retrieve Employee Name and Manager Name using SELF JOIN.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select E1.EmployeeID, E1.EmployeeName,E1.ManagerID, E2.EmployeeName as ManagerName,
E1.Salary
From EmployeeInfo E1
INNER JOIN EmployeeInfo E2 on E1.ManagerID = E2.EmployeeID;
Note: Here E1 and E2 are EmployeeInfo table alias. Since they are used to join, it is Self Join.