MySQL Self Join
MySQL SELF JOIN
A SELF JOIN is a join in which a table is joined with itself.
It is useful when you want to compare rows within the same table or find relationships among rows in the same table.
⚠️ Always use table aliases to distinguish between the two instances of the same table.
Syntax
-
a→ Alias for the first instance of the table -
b→ Alias for the second instance of the table
🧠 Example Table: employees
| emp_id | name | manager_id |
|---|---|---|
| 1 | John | NULL |
| 2 | Emma | 1 |
| 3 | Raj | 1 |
| 4 | Sara | 2 |
| 5 | Aman | 2 |
-
manager_idrefers toemp_idof another employee in the same table.
SQL Query: Find Employee and Their Manager
Result
| Employee | Manager |
|---|---|
| John | NULL |
| Emma | John |
| Raj | John |
| Sara | Emma |
| Aman | Emma |
✅ Notice:
John has no manager → Manager = NULL
Emma and Raj report to John
Sara and Aman report to Emma
Use Cases of SELF JOIN
-
Hierarchical data: Employees and managers
-
Comparisons within the same table: Find rows with similar values
-
Finding duplicates: Compare a row with other rows in the same table
Key Points
-
SELF JOIN is just a regular join with the same table used twice.
-
Always use aliases to avoid confusion.
-
Can be used with INNER JOIN, LEFT JOIN, or RIGHT JOIN.
