Home›SQL›MySQL – Self Join

MySQL – Self Join

August 9, 20244 min readUpdated 8/25/2026

A self join is a table joined to itself. It is not a separate kind of join — there is no SELF JOIN keyword — it is an ordinary JOIN where both sides happen to name the same table. Everything you know about INNER and LEFT joins applies unchanged.

The one thing that stops being optional is the alias. MySQL needs two different names to tell the two copies apart, so FROM staff, staff is an error and FROM staff a JOIN staff b is fine.

The classic: a row that points at another row

The pizza schema has no parent-child column, so here is a small staff table that does — each person's manager_id refers to another row in the same table:

CREATE TABLE staff (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80)  NOT NULL,
    role       VARCHAR(40)  NOT NULL,
    manager_id BIGINT       NULL,
    CONSTRAINT fk_staff_manager FOREIGN KEY (manager_id) REFERENCES staff (id)
);

INSERT INTO staff (id, name, role, manager_id) VALUES
    (1, 'Dana Whitfield', 'General Manager', NULL),
    (2, 'Alex Rivera',    'Shift Lead',      1),
    (3, 'Priya Nair',     'Shift Lead',      1),
    (4, 'Sam Chen',       'Driver',          2),
    (5, 'Casey Lindgren', 'Cook',            3);

The foreign key points at the table it lives in, which is legal and is what makes the shape work. Now pair each employee with their manager:

SELECT e.name AS employee, e.role, m.name AS manager
FROM   staff e
LEFT   JOIN staff m ON m.id = e.manager_id
ORDER  BY e.id;
+----------------+-----------------+----------------+
| employee       | role            | manager        |
+----------------+-----------------+----------------+
| Dana Whitfield | General Manager | NULL           |
| Alex Rivera    | Shift Lead      | Dana Whitfield |
| Priya Nair     | Shift Lead      | Dana Whitfield |
| Sam Chen       | Driver          | Alex Rivera    |
| Casey Lindgren | Cook            | Priya Nair     |
+----------------+-----------------+----------------+

LEFT JOIN, not JOIN: the General Manager has no manager, and an inner join would drop the person at the top of the tree. That is the usual bug in this query.

This walks one level. "Everyone under Dana, however deep" is a different problem — you cannot express an unbounded depth by adding joins. That needs a recursive CTE, or the closure table pattern.

Comparing rows within a table

The other everyday use is finding pairs. Which line items travelled together in the same order?

SELECT i1.order_id, i1.product_name AS item_a, i2.product_name AS item_b
FROM   order_item i1
JOIN   order_item i2 ON i2.order_id = i1.order_id AND i2.id > i1.id
ORDER  BY i1.order_id, i1.id, i2.id
LIMIT  5;
+----------+-------------------+-------------------+
| order_id | item_a            | item_b            |
+----------+-------------------+-------------------+
|        1 | Pepperoni Pizza   | Pepsi             |
|        3 | Meat Lovers Pizza | Iced Tea          |
|        6 | BBQ Chicken Pizza | Mountain Dew      |
|        8 | Pepperoni Pizza   | Bottled Water     |
|       11 | Supreme Pizza     | Meat Lovers Pizza |
+----------+-------------------+-------------------+

a.id < b.id is the whole trick

That extra condition is doing more work than it looks. Without any guard, every row matches itself, and every genuine pair appears twice — once in each direction:

SELECT
    (SELECT COUNT(*) FROM order_item a JOIN order_item b ON b.order_id = a.order_id) AS no_guard,
    (SELECT COUNT(*) FROM order_item a JOIN order_item b ON b.order_id = a.order_id AND b.id <> a.id) AS not_equal,
    (SELECT COUNT(*) FROM order_item a JOIN order_item b ON b.order_id = a.order_id AND b.id > a.id) AS ordered_pairs;
+----------+-----------+---------------+
| no_guard | not_equal | ordered_pairs |
+----------+-----------+---------------+
|       47 |        20 |            10 |
+----------+-----------+---------------+

Three different answers to what sounds like one question:

  • 47 — no guard. Includes each row paired with itself.
  • 20 — <> removes the self-pairs but keeps both directions, so (A, B) and (B, A) are both there. Exactly double the truth.
  • 10 — > keeps one row per unordered pair. This is the answer.

Use < or > when the pair is unordered ("these two went together") and <> when direction is meaningful ("A is a substitute for B" is not the same claim as the reverse).

A self join needs its indexes like any other

Both copies of the table are read independently, so the join column wants an index the same way it would across two different tables — here order_item.order_id, which the schema already indexes. Pair-finding queries also grow quadratically within each group: a self join over orders with a handful of items each is cheap, and one over a group of 10,000 rows is 50 million pairs. Filter before you pair, not after.

What to remember

  • A self join is an ordinary join; only the aliases become mandatory.
  • Use LEFT JOIN for parent lookups or you lose the row at the top.
  • a.id < b.id removes both self-pairs and mirrored duplicates.
  • One self join walks one level. Arbitrary depth needs a recursive CTE.