Advanced Joins and Sets

Self Join

Match Rows in One Table

A self join gives one table two aliases so rows can be compared with other rows from the same table.

Program

Play the script to choose a team and list each employee with their manager.

self_join_managers.sql 
CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);
INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');
WITH params(team_name) AS (VALUES ()) SELECT e.name AS employee, m.name AS manager FROM employees AS e JOIN employees AS m ON e.manager_id = m.id WHERE e.team = (SELECT team_name FROM params) ORDER BY e.name;

CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);
INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');
WITH params(team_name) AS (VALUES ()) SELECT e.name AS employee, m.name AS manager FROM employees AS e JOIN employees AS m ON e.manager_id = m.id WHERE e.team = (SELECT team_name FROM params) ORDER BY e.name;
self join Joining `employees` to itself lets one row act as the employee and another as the manager.
aliases `e` and `m` are aliases for two roles played by the same table.
nullable root The top manager has no manager row, so the inner join returns only employees with a manager.