SQL Self Join

A self join joins a table to itself. It is useful when rows in the same table are related to other rows from that same table, such as employees and managers, categories and parent categories, or people and referrals.

Syntax

SELECT a.column_name, b.column_name
FROM table_name AS a
JOIN table_name AS b
    ON a.related_column = b.id;

The aliases are required in practice. Without aliases, the query has no clear way to know which copy of the table you mean.

Example

List employees with the name of their manager:

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

Expected result

The LEFT JOIN keeps employees even when they do not have a manager. That is common for the top person in an organization chart.

Common mistakes

• Using the same column name without table aliases.
• Joining the wrong relationship column.
• Using INNER JOIN when rows without a match should still appear.

Related topics

Continue with SQL JOIN, SQL LEFT JOIN, SQL Aliases and the SQL Formatter.