MySQL Self JOIN

A Self JOIN is a regular JOIN where a table is joined with itself. This is useful when a table contains rows that are related to other rows within the same table — for example, an employee table where each employee has a manager who is also an employee in the same table.

Why Self JOIN?

Some real-world relationships exist within a single table. An organisation's hierarchy (employees and their managers) is stored in one table. A self JOIN allows reading from that table twice — once for the employee and once for the manager — and linking them together.

Syntax

SELECT a.column, b.column
FROM table_name AS a
JOIN table_name AS b ON a.related_column = b.primary_column;

The same table is given two different aliases (a and b) so MySQL treats them as two separate tables in the query.

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES (1, 'Rajesh Kumar', NULL);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 1);
INSERT INTO employees VALUES (3, 'Karan Das', 1);
INSERT INTO employees VALUES (4, 'Pooja Nair', 2);
INSERT INTO employees VALUES (5, 'Amit Roy', 2);

Here, manager_id refers to the emp_id of another employee in the same table. Rajesh Kumar (emp_id 1) is the top-level manager with no manager above him.

Example: List Employees with Their Manager Names

SELECT e.emp_name AS Employee, m.emp_name AS Manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id;

Result:

Employee     | Manager
-------------+-------------
Sneha Joshi  | Rajesh Kumar
Karan Das    | Rajesh Kumar
Pooja Nair   | Sneha Joshi
Amit Roy     | Sneha Joshi

Rajesh Kumar does not appear as an employee here because his manager_id is NULL and INNER JOIN excludes unmatched rows.

Including the Top-Level Employee with LEFT JOIN

SELECT e.emp_name AS Employee,
       IFNULL(m.emp_name, 'No Manager') AS Manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.emp_id;

Result:

Employee      | Manager
--------------+-------------
Rajesh Kumar  | No Manager
Sneha Joshi   | Rajesh Kumar
Karan Das     | Rajesh Kumar
Pooja Nair    | Sneha Joshi
Amit Roy      | Sneha Joshi

Example: Find Employees in the Same City

CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    staff_name VARCHAR(100),
    city VARCHAR(50)
);

INSERT INTO staff VALUES (1, 'Rina Das', 'Delhi');
INSERT INTO staff VALUES (2, 'Mohan Lal', 'Mumbai');
INSERT INTO staff VALUES (3, 'Kavya Rao', 'Delhi');
INSERT INTO staff VALUES (4, 'Arjun Seth', 'Mumbai');

SELECT a.staff_name AS Staff1, b.staff_name AS Staff2, a.city
FROM staff AS a
JOIN staff AS b ON a.city = b.city AND a.staff_id < b.staff_id;

Result:

Staff1     | Staff2      | city
-----------+-------------+--------
Rina Das   | Kavya Rao   | Delhi
Mohan Lal  | Arjun Seth  | Mumbai

The condition a.staff_id < b.staff_id prevents duplicate pairs like (Kavya, Rina) appearing alongside (Rina, Kavya).

Key Points

• A Self JOIN joins a table to itself using two different aliases.
• It is used when rows within the same table are related to each other.
• Common use cases include employee-manager hierarchies and finding related records in the same table.
• Use LEFT JOIN for the self join when top-level rows (with no parent) should also appear.
• Use a condition like a.id < b.id to avoid duplicate or self-paired rows in comparison queries.