What is a Self-Join in SQL?

✅ A self-join is when:

A table is joined to itself.

• We treat the same table as if it were two different tables (using aliases).
• We match rows with other rows in the same table based on a condition.

It’s not a new type of JOIN — it’s just a regular JOIN where the table joins to itself.

---

Why Use a Self-Join?

• To compare rows within the same table.
• To find relationships between rows.
• To link hierarchical data (e.g., employees and their managers).

---

Real-World Example

Imagine a table Employees:

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Dave	2

• Each employee has a manager who is also an employee (manager_id points to employee_id).

You want to find each employee and their manager’s name.

---

Self-Join Example

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

employee_name	manager_name
Alice	NULL
Bob	Alice
Charlie	Alice
Dave	Bob

✅ We use aliases e (for employee) and m (for manager) to distinguish the table used twice.

---

How It Works

• e is the current employee.
• m is the manager — but from the same table.
• Join on:

e.manager_id = m.employee_id

→ manager’s employee_id matches employee’s manager_id.

Self-Join Use Cases

Use Case	Example
Employees and managers	Employees table joins to itself.
Finding duplicates	Find rows with the same value in a table.
Finding related records	E.g., products with the same category, friends-of-friends in a social network.
Comparing rows	E.g., find rows where column A is greater than column B (in the same table).

---

Visual Diagram

Employees (alias e)         Employees (alias m)
+-------------+             +-------------+
| employee_id |             | employee_id |
| name        |             | name        |
| manager_id  |             | (other columns)
+-------------+             +-------------+

Join: e.manager_id = m.employee_id

In Short

Self-Join = Joining a table to itself using aliases, to find relationships inside the same table.

✅ Same table, treated as two different copies during the query.