SELF JOIN

#Introduction

A favourite interview topic, because it tests whether you truly understand how joins work. Unlike INNER JOIN or LEFT JOIN, a SELF JOIN is not a separate keyword:

The pattern is extremely common: employee → manager, category → parent category, comment → parent comment, organization hierarchies and family trees.

#What is a SELF JOIN?

Joining a table with itself, using different aliases, so that different rows of the same table can be related to each other. There is still only one physical table.

Rahul's manager is employee 3, Amit's manager is employee 3 and Raj's manager is employee 5. But where is the manager's name? The row stores only the manager's ID. For Rahul (manager_id = 3) we need to find the row with employee_id = 3 — Neha. Both live in the same table, which is why we use a self join.

#Why Do We Need a SELF JOIN?

Requirement: show employee name and manager name.

#Why Aliases Are Essential

SELECT *
FROM employees
JOIN employees;     -- ❌ error: Not unique table/alias: 'employees'

The same table appears twice, and SQL needs a way to distinguish the two logical roles. We use aliases: employees e (the employee) and employees m (the manager).

#SELF JOIN Syntax

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
INNER JOIN employees m
    ON e.manager_id = m.employee_id;

The join condition e.manager_id = m.employee_id means: take the employee's manager_id and find the row whose employee_id matches it.

#INNER SELF JOIN

Returns only rows that have a matching related row.

Neha and Ankit don't appear because their manager_id is NULL, so they have no matching manager row.

#LEFT SELF JOIN

Requirement: show every employee, including CEOs or top-level employees without managers.

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

INNER vs LEFT Self Join

#Category Hierarchy Example

Self joins aren't limited to employees.

Requirement: display each category with its parent category.

SELECT c.category_name AS category,
       p.category_name AS parent_category
FROM categories c
LEFT JOIN categories p
    ON c.parent_category_id = p.category_id;

Again: c = child category, p = parent category. Same table, different logical roles.

#Multiple SELF JOINs

Suppose we need employee → manager → manager's manager (CEO → Director → Manager → Developer). Join the same table multiple times:

SELECT e.employee_name  AS employee,
       m.employee_name  AS manager,
       gm.employee_name AS grand_manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id
LEFT JOIN employees gm
    ON m.manager_id = gm.employee_id;

This works well when the hierarchy depth is known. For arbitrary-depth hierarchies, a recursive CTE is generally better (covered in the CTE chapter).

#Spring Boot / JPA Example

@Entity
public class Employee {

    @Id
    private Long employeeId;

    private String employeeName;

    @ManyToOne
    @JoinColumn(name = "manager_id")
    private Employee manager;
}

The Employee entity references another Employee — a self-referencing relationship.

SELECT e
FROM Employee e
LEFT JOIN FETCH e.manager

Hibernate generates SQL similar to:

SELECT ...
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Understanding self joins helps you understand how Hibernate handles self-referencing relationships.

#Real Production Examples

#Performance Tips

1. Index the Self-Referencing Column

CREATE INDEX idx_employee_manager ON employees (manager_id);

The join frequently searches using e.manager_id = m.employee_id. employee_id is usually the primary key and already indexed; indexing manager_id also helps queries that search or group employees by manager (e.g. "direct reports").

2. Use LEFT JOIN When Top-Level Records Must Appear

If CEO.manager_id = NULL, an INNER JOIN removes the CEO. Use LEFT JOIN when top-level records should remain.

3. Use Meaningful Aliases

e → employee, m → manager, gm → grand manager; for categories c → child, p → parent. Meaningful aliases make self-join queries much easier to understand.

#Hands-On Assignment

Create employees (employee_id, employee_name, manager_id) with a CEO → Director → Manager → Developer hierarchy, then:

1. Display employee and manager.
2. Display all employees, including the CEO.
3. Display only employees without managers.
4. Display each manager and their number of direct reports.
5. Display employee, manager and manager's manager.

#Challenge Problems

Problem 1 — Employees who report directly to Rahul.

SELECT e.employee_name
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Rahul';

Problem 2 — Employees without managers.

SELECT employee_id,
       employee_name
FROM employees
WHERE manager_id IS NULL;

Problem 3 — The reporting chain up to two levels.

SELECT e.employee_name  AS employee,
       m.employee_name  AS manager,
       gm.employee_name AS grand_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.employee_id
LEFT JOIN employees gm ON m.manager_id = gm.employee_id;

Problem 4 — A category hierarchy with parent categories.

Join categories c to categories p with c.parent_category_id = p.category_id.

Problem 5 — Why is ON e.employee_id = m.employee_id incorrect?

Because it matches each employee with the same row instead of matching the employee's manager_id with the manager's employee_id. Correct: ON e.manager_id = m.employee_id.