SQL SELF JOIN
A SELF JOIN is a regular JOIN where a table is joined with itself to compare rows within the same table.
It’s commonly used for hierarchical data, like employees and managers, or products and related products.
🔹 Basic Syntax
SELECT A.column1, B.column2
FROM table_name A
JOIN table_name B
ON A.common_column = B.common_column;🔹 Example: Employees and Their Managers
Assume each employee has a manager_id referring to another employee’s employee_id in the same table.
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;✅ This shows each employee and their manager’s name.
🔹 When to Use SELF JOIN
- Compare rows in the same table
- Analyze hierarchical or paired data
- Find duplicate or related records
🧠 Quick Recap
| Key Point | Explanation |
|---|---|
SELF JOIN | A table joins with itself |
| Use case | Hierarchies, comparisons, duplicates |
| Aliases | Required to distinguish the same table used twice |
| JOIN type | Can be INNER, LEFT, etc., depending on the need |
🔁 Use SELF JOIN for comparing or relating data within a single table