Self Joins

A self join joins a table to itself under two aliases, for manager chains, neighbouring rows and row-to-row comparisons.

SyntaxSELECT ... FROM t AS a JOIN t AS b ON a.col = b.other_col

A self join is an ordinary join whose two operands happen to be the same table. There is no special syntax — you list the table twice with different aliases, and from then on MySQL treats the two aliases as unrelated tables. It exists because plain SQL compares columns within a row; when you need to compare one row to another row of the same table, you have to bring that other row alongside first.

The three shapes

Adjacency: a table with a manager_id pointing at its own id. Join e to m on m.id = e.manager_id to get each employee beside their manager. Use LEFT JOIN, or the person at the top — whose manager_id is NULL — disappears.

Neighbours: join a reading to the reading before it, on b.seq = a.seq - 1, to compute deltas.

Pairs: join a table to itself on a shared attribute to find duplicates or conflicts, with a.id < b.id so each pair is reported once instead of twice plus two self-matches.

What trips people up

Adjacency self joins only reach one level. For a whole reporting chain of unknown depth you need a recursive CTE, available from MySQL 8.0. For neighbouring rows, LAG() and LEAD() (also 8.0) are usually cheaper and do not break when a sequence number is skipped.

Example

Example · sql
-- Each employee beside their manager. The table is listed twice with
-- two aliases; MySQL then treats e and m as independent tables.
-- LEFT JOIN so the person at the top, whose manager_id is NULL,
-- is not dropped from the list.
SELECT  e.id,
        e.full_name,
        e.job_title,
        COALESCE(m.full_name, '(none)') AS manager_name
FROM    employees AS e
LEFT JOIN employees AS m
        ON m.id = e.manager_id
WHERE   e.active = 1
ORDER BY manager_name, e.full_name;

When to use it

  • Show each employee next to their manager's name from a single employees table.
  • Compute the difference between consecutive readings or the gap between consecutive events.
  • Find duplicate rows sharing an email, phone number or external id before adding a UNIQUE constraint.
  • Detect overlapping bookings, shifts or validity windows within one table.

More examples

Comparing a row to its neighbour

The self join and the window function side by side. Recognise the self-join form in older code; write the LAG form in new code, because it survives gaps in the sequence and avoids a second pass over the table. The self join is still the right tool when the 'neighbour' is defined by something a window frame cannot express.

Example · sql
-- Change in each meter reading from the previous one.
SELECT  cur.taken_at,
        cur.value,
        cur.value - prev.value AS delta
FROM    readings AS cur
JOIN    readings AS prev
        ON  prev.meter_id = cur.meter_id
        AND prev.seq      = cur.seq - 1   -- breaks if a seq is skipped
ORDER BY cur.taken_at;

-- Same answer with a window function (MySQL 8.0 and later), which
-- does not depend on the sequence being gapless and reads the table once.
SELECT  taken_at,
        value,
        value - LAG(value) OVER (PARTITION BY meter_id ORDER BY seq) AS delta
FROM    readings
ORDER BY taken_at;

Finding duplicate and conflicting rows

The asymmetric key comparison is the part worth memorising: without b.id > a.id every row matches itself and every genuine pair appears twice, in both orders. Use this before adding a UNIQUE constraint to an existing table, and to audit scheduling data for overlaps.

Example · sql
-- Customer records sharing an email address.
-- a.id < b.id reports each pair once and excludes a row matching itself.
SELECT  a.id AS keep_id,
        b.id AS dupe_id,
        a.email
FROM    customers AS a
JOIN    customers AS b
        ON  b.email = a.email
        AND b.id    > a.id
ORDER BY a.email;

-- Same idea for overlapping bookings on one room.
SELECT r1.id, r2.id, r1.room_id
FROM   reservations AS r1
JOIN   reservations AS r2
       ON  r2.room_id  = r1.room_id
       AND r2.id       > r1.id
       AND r2.starts_at < r1.ends_at      -- half-open overlap test
       AND r1.starts_at < r2.ends_at;

Two levels up, and when to stop

Each extra self join buys exactly one more level, so a chain of them only works when the depth is fixed and known. As soon as the hierarchy is of arbitrary depth — an org chart, a category tree, a bill of materials — the recursive CTE is the correct tool and the self-join chain becomes unmaintainable.

Example · sql
-- Employee, manager, and manager's manager: two self joins.
SELECT  e.full_name  AS employee,
        m.full_name  AS manager,
        mm.full_name AS skip_level
FROM    employees AS e
LEFT JOIN employees AS m  ON m.id  = e.manager_id
LEFT JOIN employees AS mm ON mm.id = m.manager_id;

-- Beyond a fixed depth, stop chaining and recurse instead (MySQL 8.0+).
WITH RECURSIVE chain AS (
    SELECT id, full_name, manager_id, 1 AS depth
    FROM   employees
    WHERE  id = 42                       -- anchor: the starting employee
    UNION ALL
    SELECT e.id, e.full_name, e.manager_id, c.depth + 1
    FROM   chain AS c
    JOIN   employees AS e ON e.id = c.manager_id
)
SELECT * FROM chain ORDER BY depth;

Related

sql recursive ctesql hierarchiessql lag leadsql left joinsql inner join

Discussion

  • Be the first to comment on this lesson.