SQL Self Join

A Self Join joins a table with itself. The table is treated as two separate tables using different aliases.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
JOIN employees AS m
ON e.manager_id = m.employee_id;

Here, the employees table is used twice: once for employees and once for managers.

A Self Join is useful when records in the same table have relationships with other records in that table.

• Employees and managers
• Students and mentors
• Categories and parent categories
• Employees and supervisors
• Products and related products

The basic syntax is:

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

The same table is given two different aliases so SQL can distinguish between the two references.

Suppose an employees table contains manager_id.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
JOIN employees AS m
ON e.manager_id = m.employee_id;

The first alias represents the employee and the second alias represents the manager.

Aliases are essential in a Self Join because the same table appears twice.

FROM employees AS e
JOIN employees AS m

Here:

• e = employee
• m = manager

The ON condition defines how records within the same table are related.

ON e.manager_id = m.employee_id

This means the manager ID stored for an employee must match another employee's employee ID.

A LEFT JOIN can be used when you want to display every employee, including employees who do not have a manager.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;

Employees without a manager will have a NULL manager name.

An INNER JOIN returns only employees who have a matching manager.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;

You can select additional columns from both references of the same table.

SELECT
    e.employee_id,
    e.employee_name,
    m.employee_id AS manager_id,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;

A WHERE condition can filter records from a Self Join.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE e.department = 'IT';

This displays employees from the IT department and their managers.

The result can be sorted using columns from the employee reference.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id
ORDER BY e.employee_name;

A LEFT JOIN can find employees who do not have a manager.

SELECT
    e.employee_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE e.manager_id IS NULL;

This can identify top-level employees or employees whose manager has not been assigned.

You can find employees who have a manager using a condition on manager_id.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE e.manager_id IS NOT NULL;

A Self Join can display a simple organizational hierarchy.

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

The result shows each employee and the manager assigned to that employee.

A Self Join can compare the salary of an employee with the salary of another employee.

SELECT
    e.employee_name,
    e.salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;

A Self Join can compare two rows from the same table.

SELECT
    e.employee_name,
    e.salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

This returns employees whose salary is greater than their manager's salary.

The same technique can find employees whose salary is lower than their manager's salary.

SELECT
    e.employee_name,
    e.salary,
    m.employee_name AS manager_name,
    m.salary AS manager_salary
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id
WHERE e.salary < m.salary;

A Self Join can find employees working in the same department.

SELECT
    e1.employee_name AS employee1,
    e2.employee_name AS employee2,
    e1.department
FROM employees AS e1
INNER JOIN employees AS e2
ON e1.department = e2.department
AND e1.employee_id < e2.employee_id;

The ID condition helps avoid returning the same employee pair in reverse order.

Self Join allows us to compare values from two different rows of the same table.

SELECT
    a.employee_name AS employee_a,
    b.employee_name AS employee_b,
    a.salary AS salary_a,
    b.salary AS salary_b
FROM employees AS a
INNER JOIN employees AS b
ON a.department = b.department
AND a.employee_id < b.employee_id;

CASE can be combined with Self Join to classify relationships.

SELECT
    e.employee_name,
    m.employee_name AS manager_name,
    CASE
        WHEN e.salary > m.salary THEN 'Higher Salary'
        WHEN e.salary < m.salary THEN 'Lower Salary'
        ELSE 'Same Salary'
    END AS salary_status
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;

Self Join can be combined with aggregate functions to count related records.

SELECT
    m.employee_name AS manager,
    COUNT(e.employee_id) AS team_size
FROM employees AS m
LEFT JOIN employees AS e
ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.employee_name;

This counts the number of employees reporting to each manager.

GROUP BY can be used to create manager-wise reports.

SELECT
    m.employee_name AS manager,
    COUNT(e.employee_id) AS total_employees
FROM employees AS m
LEFT JOIN employees AS e
ON e.manager_id = m.employee_id
GROUP BY m.employee_name;

HAVING can filter managers based on the number of employees reporting to them.

SELECT
    m.employee_name AS manager,
    COUNT(e.employee_id) AS team_size
FROM employees AS m
LEFT JOIN employees AS e
ON e.manager_id = m.employee_id
GROUP BY m.employee_name
HAVING team_size > 5;

This returns managers with more than five employees in their team.

Self Join is not limited to employees. It is also useful for parent-child relationships.

SELECT
    child.category_name AS category,
    parent.category_name AS parent_category
FROM categories AS child
LEFT JOIN categories AS parent
ON child.parent_id = parent.category_id;

Here, the same categories table stores both categories and parent categories.

A Self Join can be used when products are related to other products in the same table.

SELECT
    p.product_name,
    r.product_name AS related_product
FROM products AS p
LEFT JOIN products AS r
ON p.related_product_id = r.product_id;

When comparing records within the same table, the same pair can appear twice. A condition such as a.id < b.id can prevent duplicate pairs.

SELECT
    a.employee_name AS employee1,
    b.employee_name AS employee2
FROM employees AS a
INNER JOIN employees AS b
ON a.department = b.department
AND a.employee_id < b.employee_id;

A common mistake is using the same alias for both references to the table.

-- Incorrect
FROM employees AS e
JOIN employees AS e

Each reference must have a different alias.

-- Correct
FROM employees AS e
JOIN employees AS m

Normal JOIN: Usually joins different tables.

Self Join: Joins a table with itself using different aliases.

-- Normal JOIN
FROM students s
JOIN courses c
ON s.course_id = c.id;

-- Self JOIN
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

A practical employee-manager report can be created using a Self Join.

SELECT
    e.employee_id,
    e.employee_name AS employee,
    e.department,
    e.salary,
    m.employee_name AS manager,
    m.salary AS manager_salary
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id
ORDER BY e.employee_name;

This report displays each employee and the manager assigned to them.

The following example combines a Self Join with CASE and calculations.

SELECT
    e.employee_id,
    e.employee_name AS employee,
    e.department,
    e.salary AS employee_salary,
    m.employee_name AS manager,
    m.salary AS manager_salary,
    CASE
        WHEN e.manager_id IS NULL THEN 'Top Level'
        WHEN e.salary > m.salary THEN 'Higher Than Manager'
        WHEN e.salary < m.salary THEN 'Lower Than Manager'
        ELSE 'Same As Manager'
    END AS employee_status
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id
ORDER BY e.employee_name;

This query displays the employee, manager, salaries, and a calculated relationship status.