SQL - Self Join

SQL: The Self Join

A Self Join is a regular join, but instead of joining two different tables, a table is joined to itself. This technique is powerful for querying hierarchical data or finding relationships between rows within the same table.

To perform a self join, you must use table aliases to give the table two different names within the query. This allows the database to treat it as two separate tables, enabling you to compare columns from different rows.

Modern Syntax for Self Join

A self join uses the standard JOIN syntax. The key is to alias the table and define a logical join condition between the two aliases.

It is a modern best practice to use the explicit JOIN ... ON syntax rather than the older, implicit comma-based syntax in the WHERE clause. This makes queries more readable and less prone to errors.

Syntax

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

A Practical Example: Employees and Managers

The most common use case for a self join is querying an employees table where one column stores the employee’s ID and another stores their manager’s ID. Since the manager is also an employee, the manager’s ID refers back to another row in the same table.

Let’s create an Employees table:

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INT -- This can be NULL for the top-level employee
);

INSERT INTO Employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice Smith (CEO)', NULL),
(2, 'Bob Johnson', 1),
(3, 'Charlie Brown', 1),
(4, 'Diana Prince', 2);

The table looks like this:

employee_id	employee_name	manager_id
1	Alice Smith (CEO)	NULL
2	Bob Johnson	1
3	Charlie Brown	1
4	Diana Prince	2

To get a list of each employee and their manager’s name, we can join the Employees table to itself. We’ll use the alias e for the employee and m for the manager.

SELECT
    e.employee_name AS "Employee Name",
    m.employee_name AS "Manager Name"
FROM
    Employees AS e
INNER JOIN
    Employees AS m ON e.manager_id = m.employee_id;

Output

This query effectively links each employee to their manager by name:

Employee Name	Manager Name
Bob Johnson	Alice Smith (CEO)
Charlie Brown	Alice Smith (CEO)
Diana Prince	Bob Johnson

Using a LEFT JOIN for a Self Join

Notice that the CEO, Alice Smith, did not appear in the previous result’s ‘Employee Name’ column. This is because INNER JOIN only includes rows where the join condition is met, and her manager_id is NULL. To include all employees, even those without a manager, we can use a LEFT JOIN.

SELECT
    e.employee_name AS "Employee Name",
    COALESCE(m.employee_name, 'No Manager') AS "Manager Name"
FROM
    Employees AS e
LEFT JOIN
    Employees AS m ON e.manager_id = m.employee_id
ORDER BY
    e.employee_id;

Note: We use the COALESCE function to display ‘No Manager’ when the manager’s name is NULL, making the output cleaner.

Output

Now the result includes every employee:

Employee Name	Manager Name
Alice Smith (CEO)	No Manager
Bob Johnson	Alice Smith (CEO)
Charlie Brown	Alice Smith (CEO)
Diana Prince	Bob Johnson

Best Practices

• Always Use Aliases: Aliases are mandatory for a self join. Use meaningful aliases (e.g., e for employee, m for manager) to improve readability.
• Use Explicit JOIN Syntax: Always use JOIN ... ON syntax. It clearly separates the join logic from the filtering logic (WHERE clause).
• Choose the Right JOIN Type: Use INNER JOIN if you only want rows that have a relationship. Use LEFT JOIN if you want all rows from one side of the relationship, regardless of whether a match exists on the other side.