SELF JOIN

Join the same table using two different aliases.

SELECT a.column, b.column
FROM table AS a
JOIN table AS b ON a.join_key = b.join_key;

The following employees table is used in the examples below. The manager_id column references the employee_id column in the same table.

Retrieve each employee and their manager's name from the employee table.

sample_self_join.sql

SELECT
    emp.name AS employee,
    mgr.name AS manager
FROM employees AS emp
LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id;

+----------+---------+
| employee | manager |
+----------+---------+
| user_a   | NULL    |
| user_c   | user_a  |
| user_e   | user_c  |
| user_b   | user_c  |
+----------+---------+
4 rows in set

List all pairs of employees in the same department (restrict order by employee ID to avoid duplicates).

sample_self_join.sql

SELECT
    a.name AS employee_a,
    b.name AS employee_b,
    a.department
FROM employees AS a
JOIN employees AS b
    ON a.department = b.department
    AND a.employee_id < b.employee_id
ORDER BY a.department, a.employee_id;

+------------+------------+------------+
| employee_a | employee_b | department |
+------------+------------+------------+
| user_a     | user_c     | org_a      |
| user_a     | user_e     | org_a      |
| user_a     | user_b     | org_a      |
| user_c     | user_e     | org_a      |
| user_c     | user_b     | org_a      |
| user_e     | user_b     | org_a      |
+------------+------------+------------+
6 rows in set

The self-join syntax works the same way across all major databases.

-- Common to MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT emp.name AS employee_name, mgr.name AS manager_name
FROM employees AS emp
LEFT JOIN employee AS mgr ON emp.manager_id = mgr.employee_id;

In Oracle, the AS keyword cannot be used for table aliases. Omit AS when specifying a table alias.

-- In Oracle, do not use AS for table aliases
SELECT emp.name AS employee_name, mgr.name AS manager_name
FROM employees emp
LEFT JOIN employee mgr ON emp.manager_id = mgr.employee_id;

SELF JOIN is not a special SQL syntax — it is simply a regular JOIN applied to the same table. You must always assign table aliases; without them, column references become ambiguous and will cause an error.

There are two main use cases. The first is retrieving parent-child relationships in hierarchical data, such as employees and their managers, or categories and their parent categories. The second is comparing rows within the same table, such as listing employee pairs in the same department or comparing sales across consecutive dates.

If you also want to include rows with no parent (such as top-level employees), use LEFT JOIN to retrieve them as NULL. For the difference between self-joins and CROSS JOIN, see CROSS JOIN.