SQL Self Join
A SELF JOIN is a regular join in which a table is joined with itself.
It is not a separate SQL keyword; instead, you use standard join syntax (like INNER JOIN or LEFT JOIN) but reference the exact same table name twice. To do this without causing a syntax error, you must use distinct table aliases to give each instance of the table a unique name.
Why Join a Table to Itself?
A self join is necessary when a table contains a hierarchical or unary relationship, meaning a column in a row references another column within the same table.
The two most common real-world examples are:
- Organizational Charts: An employees table where a manager_id column points back to the employee_id of their boss.
- Category Trees: A categories table where a parent_category_id points to a primary category (e.g., "Laptops" belongs under "Electronics").
Basic Syntax Blueprint
SELECT a.column_name, b.column_name
FROM table_name AS a
INNER JOIN table_name AS b ON a.common_column = b.common_column;
A Real-World Walkthrough (The Employee-Manager Hierarchy)
Imagine we have an employees table where every worker has a manager who is also listed in the same table.
employees Table
| employee_id | name | manager_id |
|---|---|---|
| 1 | Sunita (CEO) | NULL |
| 2 | Rajesh | 1 |
| 3 | Vikram | 1 |
| 4 | Ananya | 2 |
The Self Join Query:
To find out who manages whom, we treat alias e as the employee list and alias m as the manager list.
SELECT a.column_name, b.column_name
FROM table_name AS a
INNER JOIN table_name AS b ON a.common_column = b.common_column;
The Output Result:
| Employee Name | Manager Name |
|---|---|
| Rajesh | Sunita (CEO) |
| Vikram | Sunita (CEO) |
| Ananya | Rajesh |
Why is Sunita missing from the output?
Because we used an INNER JOIN, and Sunita's manager_id is NULL. If you want to keep the top-level executives in the report, switch the query to a LEFT JOIN (FROM employees AS e LEFT JOIN employees AS m ...).
Practical Use Case: Finding Duplicates
You can also use a self join to look for rows in a table that share identical data points but have distinct primary keys.
Example: Find customers who share the same email address
SELECT a.customer_id, a.name, b.customer_id, b.name, a.email
FROM customers AS a
INNER JOIN customers AS b ON a.email = b.email
WHERE a.customer_id < b.customer_id; -- Prevents matching a row to itself and hides duplicate pairs