SELF JOIN

Introduction

Every join so far has connected two different tables. But what happens when the relationship you need actually lives inside a single table — like an employee whose manager is also an employee, in that very same table? That is exactly the problem a SELF JOIN solves.

This lesson explains what a self join is, why it requires table aliases, and walks through a full worked example connecting employees to their managers.

What is a SELF JOIN?

A self join is simply a regular join (usually INNER JOIN or LEFT JOIN) where a table is joined to itself. MySQL does not have a separate SELF JOIN keyword — you just reference the same table twice in the same query, giving each reference its own alias so MySQL (and you) can tell them apart.

Self joins are most common with hierarchical or comparative data: employees and managers, categories and subcategories, or comparing rows within the same table (like finding pairs of products with the same price).

The Sample Table

Consider an "employees" table where each row has a manager_id column, pointing to the id of another employee in that same table.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'Rita', NULL),
    (2, 'Alex', 1),
    (3, 'Sam', 1),
    (4, 'Priya', 2),
    (5, 'Owen', 2);

Rita (id 1) has manager_id NULL, meaning she has no manager — perhaps she is the CEO. Alex and Sam both report to Rita. Priya and Owen both report to Alex.

Why Aliases Are Required

If we tried to write "FROM employees JOIN employees" without aliases, MySQL would not be able to tell the two references to the employees table apart, and the query would fail. Aliases solve this by giving each reference to the table a distinct, temporary name for the duration of the query.

A common convention is to alias one copy as "e" (the employee) and the other as "m" (the manager), which also makes the query easier to read.

Basic Syntax

SELECT e.column, m.column
FROM employees AS e
JOIN employees AS m
    ON e.manager_id = m.id;

Here, "e" represents each employee row, and "m" represents the row for that employee's manager — both coming from the exact same underlying table.

Worked Example

Let's list every employee alongside the name of their manager, by joining employees to itself on manager_id = id.

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id;

+---------------+---------------+
| employee_name | manager_name  |
+---------------+---------------+
| Rita          | NULL          |
| Alex          | Rita          |
| Sam           | Rita          |
| Priya         | Alex          |
| Owen          | Alex          |
+---------------+---------------+

We deliberately used LEFT JOIN instead of INNER JOIN here. Rita has no manager (manager_id is NULL), so an INNER JOIN would have excluded her entirely from the results. LEFT JOIN keeps every employee, showing NULL for manager_name when there is no manager to match.

Finding Employees With No Manager

Combining what we learned about LEFT JOIN in an earlier lesson with a self join, we can specifically find employees who have no manager at all.

SELECT e.name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
WHERE m.id IS NULL;

+------+
| name |
+------+
| Rita |
+------+

This is the same LEFT JOIN + IS NULL pattern from an earlier lesson, just applied to a table joined with itself.

Other Uses of SELF JOIN

Manager/employee hierarchies are the classic example, but self joins solve several other common problems too.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

A self join is not a different kind of join technically — it is an ordinary INNER JOIN or LEFT JOIN, just pointed at the same table twice with two clear aliases. It is the standard tool for hierarchical data like employees and managers.

In this lesson, you learned why aliases are required for a self join, worked through listing every employee with their manager's name, and saw a few other real-world uses for this pattern. Next, you will learn UNION and UNION ALL, which combine the results of separate queries instead of combining columns from related tables.