MySQL SELF JOIN: Syntax and Examples

A self join uses a table twice in the same query to compare or relate rows in that table. MySQL does not have a separate SELF JOIN keyword; write a regular JOIN and give each table reference its own alias. For the general join forms, see MySQL JOIN.

MySQL SELF JOIN syntax

SELECT left_alias.column_name, right_alias.column_name
FROM table_name AS left_alias
JOIN table_name AS right_alias
    ON left_alias.key_column = right_alias.key_column;

The aliases distinguish the two roles played by the same table. Always qualify column names with the correct alias when both references contain columns with the same names.

Match employees with their managers

An employee table can store each manager as another employee’s ID. Create and populate a small example table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(80) NOT NULL,
    manager_id INT NULL
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
    (1, 'Ava Chen', NULL),
    (2, 'Leo Kim', 1),
    (3, 'Mia Park', 1),
    (4, 'Noah Liu', 2),
    (5, 'Ivy Wu', 2);

Use employees once as the employee (e) and again as the manager (m):

SELECT
    e.employee_id,
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

The query returns:

+-------------+---------------+--------------+
| employee_id | employee_name | manager_name |
+-------------+---------------+--------------+
|           1 | Ava Chen      | NULL         |
|           2 | Leo Kim       | Ava Chen     |
|           3 | Mia Park      | Ava Chen     |
|           4 | Noah Liu      | Leo Kim      |
|           5 | Ivy Wu        | Leo Kim      |
+-------------+---------------+--------------+

The LEFT JOIN keeps Ava’s row even though she has no manager. Replacing it with INNER JOIN returns only employees that have a matching manager row.

Find pairs of rows in the same table

A self join can also find pairs of products that share a category. The condition p1.product_id < p2.product_id prevents a product from matching itself and returns each pair only once:

SELECT
    p1.product_id AS product_a,
    p2.product_id AS product_b,
    p1.category_id
FROM products AS p1
JOIN products AS p2
    ON p1.category_id = p2.category_id
   AND p1.product_id < p2.product_id;

This query assumes product_id uniquely identifies each product. Without the < condition, the result could include each product paired with itself and both orders of the same pair.

Common SELF JOIN mistakes

• Reusing the table name without aliases: Give every reference to the table a distinct alias, such as e and m.
• Leaving columns unqualified: Use e.employee_id or m.employee_id so MySQL knows which reference you mean.
• Choosing the wrong join type: Use LEFT JOIN when rows without a match must remain; use INNER JOIN when only matched pairs are needed.
• Joining on an incomplete condition: Include the key comparison in ON; an omitted join condition can create many unintended row combinations.

A self join connects rows using one relationship at a time. To walk a hierarchy with an unknown number of levels, use a recursive CTE.

For the table-alias and join syntax, see the MySQL 8.4 Reference Manual: JOIN Clause and Using More Than One Table.