- As the name signifies, in SELF JOIN a table is joined to
itself.
- That is, each row of the table is joined with itself and all other rows depending on some conditions.
- In other words we can say that it is a join between two copies of the same table.
- Compare rows within the same table.
- Find relationships within the table (e.g., employees and their managers, products and alternatives)
SELECT A.column1, B.column2 FROM TableName A
JOIN TableName B
ON A.common_column = B.related_column;
- Use table aliases (A and B) to differentiate between the two instances of the same table.
=============================================================================
| emp_id | emp_name | manager_id |
|---|---|---|
| 1 | John | NULL |
| 2 | Alice | 1 |
| 3 | Bob | 1 |
| 4 | Carol | 2 |
SELECT e.emp_name AS Employee, m.emp_name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
| Employee | Manager |
|---|---|
| John | NULL |
| Alice | John |
| Bob | John |
| Carol | Alice |
When to Use Self Join:
- To find hierarchies (e.g., org charts)
- To compare rows within a table
- To query relational patterns (e.g., one row refers to another row in the same table)
