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.

team_name
self_join_managers.sql
Replay: real traced execution (multi-file project)
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 ('Platform')) 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 ('Data')) 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;

  1. tables ← 1 row

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');
    values this step1 rowtables

  2. employees ← 4 rows

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');3WITH params(team_name) AS (VALUES ('Platform')) 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;
    values this step4 rowsemployees

  3. result ← 1 row

    2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');3WITH params(team_name) AS (VALUES ('Platform')) 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;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');
    values this step1 rowtables

  2. employees ← 4 rows

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER, team TEXT);2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');3WITH params(team_name) AS (VALUES ('Data')) 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;
    values this step4 rowsemployees

  3. result ← 2 rows

    2INSERT INTO employees VALUES (1, 'Ada', NULL, 'Platform'), (2, 'Lin', 1, 'Platform'), (3, 'Nia', 1, 'Data'), (4, 'Mira', 3, 'Data');3WITH params(team_name) AS (VALUES ('Data')) 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;
    values this step2 rowsresult
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.