Self Join

Self Joins are when we join the table to itself, they are used when their is usually a reference to existing column. To use Self Join we need to create different alias for the same table and use JOIN statement to perform self join operation.

For example we have table called EmployeeMaster with employee information and a column to describe who this employee reports to basically a manager id for each employee and we want to get the list of employees with their manager name.

CREATE TABLE EmployeeMaster
(
EmployeeId 
,
FirstName Text,
LastName Text,
DepartmentId Integer,
ReportsTo Integer
);

INSERT INTO EmployeeMaster values (1,'John','Dekota', 2,7);
INSERT INTO EmployeeMaster values (2,'Mary','Denware',3,2);
INSERT INTO EmployeeMaster values (3,'Scott','Hamilton',2,7);
INSERT INTO EmployeeMaster values (4,'Guru','Manna',3,2);
INSERT INTO EmployeeMaster values (5,'Robert','Olipo',1,3);
INSERT INTO EmployeeMaster values (6,'Mark','Netcal',2,7);
INSERT INTO EmployeeMaster values (7,'Peter','adam',3,2);
INSERT INTO EmployeeMaster values (8,'Martin','Jerico',2,7);
INSERT INTO EmployeeMaster values (9,'Davis','Ocee',1,3);

The following sql statement provides example code to create self join, notice FROM EmployeeMaster a JOIN EmployeeMaster b we are joining EmployeeMaster table with itself under two different alias a and b to match ON a.ReportsTo = b.EmployeeId who is reporting to which employee.

SELECT a.EmployeeId, a.FirstName || ' ' || a.LastName as Employee
, b.EmployeeId as 'ReportsTo', b.FirstName || ' ' || b.LastName as Manager
FROM EmployeeMaster a JOIN EmployeeMaster b
ON a.ReportsTo = b.EmployeeId

What if we want to know the count of individuals who are under each manager. We can use the same table also in subquery to get the count with where clause.

SELECT a.EmployeeId, a.FirstName || ' ' || a.LastName as Employee
, b.EmployeeId as 'ReportsTo', b.FirstName || ' ' || b.LastName as Manager
, (
  SELECT Count(*) FROM EmployeeMaster 
  Where ReportsTo = a.ReportsTo
  ) [TotalStaffUnderManager]
FROM EmployeeMaster a JOIN EmployeeMaster b
ON a.ReportsTo = b.EmployeeId