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;

-- Retrieve each employee and their manager's name from the employee table.
SELECT
    emp.employee_name AS employee_name,
    mgr.employee_name AS manager_name
FROM employee AS emp
LEFT JOIN employee AS mgr ON emp.manager_id = mgr.employee_id;

-- List all pairs of employees in the same department (restrict order by employee ID to avoid duplicates).
SELECT
    a.employee_name AS employee_a,
    b.employee_name AS employee_b,
    a.department
FROM employee AS a
JOIN employee AS b
    ON a.department = b.department
    AND a.employee_id < b.employee_id
ORDER BY a.department, a.employee_id;

-- Manager name results (NULL if the employee has no manager).
employee_name | manager_name
--------------+--------------
Taro Yamada   | NULL
Hanako Suzuki | Taro Yamada
Jiro Sato     | Taro Yamada
Megumi Tanaka | Hanako Suzuki

-- Employee pairs in the same department.
employee_a    | employee_b   | department
--------------+--------------+------------
Hanako Suzuki | Jiro Sato    | Sales

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

-- Common to MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT emp.employee_name AS employee_name, mgr.employee_name AS manager_name
FROM employee 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.employee_name AS employee_name, mgr.employee_name AS manager_name
FROM employee 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.