'Self Join' is a type of join that joins a table to itself. It is like having a table, a copy of that table and
joining the copy to the original table.
The Self Join
The 'Self Join 'is a type of inner join. Here you are joining a table to itself. This type of join is normally used on a table
that has a foreign key column and the primary key column in itself. So here the foreign key references its primary key. Since you are
joining a table to itself, it is identical to joining two tables as they both have the same name. To distinguish the columns from each table,
aliases are used for both tables.
SQL 'Self Join' Syntax
SELECT a.column1, b.column1,a.column2, b.column2,...
FROM table1 a, table1 b
WHERE a.columnname = b.columnname
The Self Join Example
Let us take the 'EmployeeInfo' table as reference.This table contains the 'EmployeeName' as well as the 'ManagerID'
as shown below.
Select EmployeeID, EmployeeName,ManagerID,Designation
from EmployeeInfo
1 |
Georg Pipps |
NULL |
CEO |
2 |
Rene Phillips |
1 |
Sales Head |
3 |
Elizabeth Brown |
1 |
HR Manager |
4 |
Hari Kumar |
1 |
Tech Head |
5 |
Howard Snyder |
2 |
Sales Officer |
6 |
Patricio Simpson |
2 |
Sales Officer |
7 |
Yang Wang |
3 |
HR |
8 |
Thomas Hardy |
3 |
HR |
9 |
Antonio Moreno |
4 |
Programmer |
10 |
Martine Rancé |
4 |
Programmer |
Note: Here 'Georg Pipps' being the CEO , doesnt have a manager.
Now to display the employee name, along with his manager name instead of the 'ManagerID', the 'EmployeeInfo' table
must be joined to itself and given an alias name.
2 |
Rene Phillips |
1 |
Georg Pipps |
Sales Head |
3 |
Elizabeth Brown |
1 |
Georg Pipps |
HR Manager |
4 |
Hari Kumar |
1 |
Georg Pipps |
Tech Head |
5 |
Howard Snyder |
2 |
Rene Phillips |
Sales Officer |
6 |
Patricio Simpson |
2 |
Rene Phillips |
Sales Officer |
7 |
Yang Wang |
3 |
Elizabeth Brown |
HR |
8 |
Thomas Hardy |
3 |
Elizabeth Brown |
HR |
9 |
Antonio Moreno |
4 |
Hari Kumar |
Programmer |
10 |
Martine Rancé |
4 |
Hari Kumar |
Programmer |
SELECT a.EmployeeID, a.EmployeeName, b.ManagerID,
b.EmployeeName AS "ManagerName",a.Designation
FROM EmployeeInfo a, EmployeeInfo b
WHERE a.ManagerID = b.EmployeeID
You can even use the below code to obtain the same results using an Inner Join.
SELECT a.EmployeeID, a.EmployeeName, b.ManagerID,
b.EmployeeName AS "ManagerName", a.Designation
FROM EmployeeInfo a
INNER JOIN EmployeeInfo b on a.ManagerID= b.EmployeeID