SQL Self Join

Download SQL for free

Example

A table may be joined to itself, with different rows matching each other by some condition. In this use case, aliases must be used in order to distinguish the two occurrences of the table.

In the below example, for each Employee in the example database Employees table, a record is returned containing the employee's first name together with the corresponding first name of the employee's manager. Since managers are also employees, the table is joined with itself:

SELECT 
    e.FName AS "Employee", 
    m.FName AS "Manager"
FROM   
    Employees e
JOIN   
    Employees m 
    ON e.ManagerId = m.Id

This query will return the following data:

EmployeeManager
JohnJames
MichaelJames
JohnathonJohn

So how does this work?

The original table contains these records:

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHireDate
1JamesSmith1234567890NULL1100001-01-2002
2JohnJohnson24681012141140023-03-2005
3MichaelWilliams13579111311260012-05-2009
4JohnathonSmith12121212122150024-07-2016

The first action is to create a Cartesian product of all records in the tables used in the FROM clause. In this case it's the Employees table twice, so the intermediate table will look like this (I've removed any fields not used in this example):

e.Ide.FNamee.ManagerIdm.Idm.FNamem.ManagerId
1JamesNULL1JamesNULL
1JamesNULL2John1
1JamesNULL3Michael1
1JamesNULL4Johnathon2
2John11JamesNULL
2John12John1
2John13Michael1
2John14Johnathon2
3Michael11JamesNULL
3Michael12John1
3Michael13Michael1
3Michael14Johnathon2
4Johnathon21JamesNULL
4Johnathon22John1
4Johnathon23Michael1
4Johnathon24Johnathon2

The next action is to only keep the records that meet the JOIN criteria, so any records where the aliased e table ManagerId equals the aliased m table Id:

e.Ide.FNamee.ManagerIdm.Idm.FNamem.ManagerId
2John11JamesNULL
3Michael11JamesNULL
4Johnathon22John1

Then, each expression used within the SELECT clause is evaluated to return this table:

e.FNamem.FName
JohnJames
MichaelJames
JohnathonJohn

Finally, column names e.FName and m.FName are replaced by their alias column names, assigned with the AS operator:

EmployeeManager
JohnJames
MichaelJames
JohnathonJohn
LIKE operator