SELF JOIN

Selft join is a particular type of join. The difference is that the table pairs on itself. It is very often used, for example, to find an employee and his superior.

Structure:

SELECT column_name
FROM table_name alias1, table_name alias2
WHERE condition;

 

Example1:

We have table with this data:

Table Staff

 

In the Staff table, the StaffID expresses the employee identifier in general. However, some employees are also superiors. We see this superiority in the SupervisorId field. This means that Frank has a superior StaffId = 3. It is Julia. Helena has the same superior. Julia has no superior. She is a boss. Thomas has a superior, Helena.

 

SELECT t1. StaffID, t1. Staff_Name AS "Staff Name", t2. StaffID AS "Supervisor ID", t2. Staff_Name AS "Supervisor Name"
FROM Staff t1, Staff t2
WHERE t1. SupervisorId= t2. StaffID;