Page 124 - SQL

Self Join

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:

Employee Manager

John James

Michael James

Johnathon John

So how does this work?

The original table contains these records:

Id FName LName PhoneNumber ManagerId DepartmentId Salary HireDate

01-01-
1 James Smith 1234567890 NULL 1 1000
2002

23-03-
2 John Johnson 2468101214 1 1 400
2005

12-05-
3 Michael Williams 1357911131 1 2 600
2009

24-07-
4 Johnathon Smith 1212121212 2 1 500
2016

The first action is to create a Cartesian product of all records in the tables used in the FROM

https://riptutorial.com/ 106