MySQL Self Join

A Self Join joins a table to itself.

For example, an employees table may contain both employees and their managers:

employees
----------------
employee_id
employee_name
manager_id

The manager_id can refer to another employee_id in the same table.

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

• Employees and managers
• Employees and supervisors
• Categories and parent categories
• Employees in the same department
• People and their mentors
• Products and related products

SELECT
    a.column1,
    b.column2
FROM table_name a
INNER JOIN table_name b
ON a.column = b.column;

The same table is written twice with different aliases.

When a table is used more than once in a query, aliases help MySQL distinguish between the different references to the table.

FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

Here:

• e represents the employee.
• m represents the manager.

Consider this employees table:

Here:

• Raj has no manager.
• Amit reports to Raj.
• Priya reports to Raj.
• Rahul reports to Amit.

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

This joins the employees table with itself to display employees and their managers.

In the following query:

FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

The same table is used twice:

• employees e = employee records
• employees m = manager records

The manager_id of one row matches the employee_id of another row.

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

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

Employees without a manager will have NULL in the manager column.

Using the previous employee table, the result can look like:

Raj is included because LEFT JOIN keeps all employee rows.

Self Join can find employees who do not have a manager.

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

This returns employees whose manager_id is NULL.

You can filter the manager after joining the table.

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Raj';

This returns employees who report directly to Raj.

You can display both employee and manager IDs.

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

This is useful for understanding hierarchical relationships.

ORDER BY can sort the employee-manager result.

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

This groups employees according to their managers.

WHERE can be used to filter employee-manager relationships.

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.employee_name LIKE 'A%';

This returns employees whose names start with A and their managers.

Self Join can compare values between two rows of the same table.

Suppose an employees table contains salaries.

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

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

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

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

This can find pairs of employees belonging to the same department without returning the same pair twice in reverse order.

When comparing rows in the same table, the condition:

e1.employee_id < e2.employee_id

helps avoid pairs such as:

Amit - Priya
Priya - Amit

Only one pair is returned.

Suppose employees have a department column.

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

This returns pairs of employees working in the same department.

Self Join is commonly used for hierarchical data.

For example, a categories table can contain:

category_id
category_name
parent_id

The parent_id can refer to another category_id in the same table.

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

Here:

• Electronics is a parent category.
• Mobiles and Laptops belong to Electronics.
• Android Phones belongs to Mobiles.

Self Join can also be combined with aggregate functions.

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

This counts the employees who directly report to each manager.

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

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

This returns managers with more than two direct employees.

The ON clause can contain multiple conditions.

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

This finds employees whose manager belongs to the same department.

Self Join can compare dates between rows of the same table.

For example, suppose an employees table contains joining_date:

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager,
    e.joining_date,
    m.joining_date AS manager_joining_date
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.joining_date > m.joining_date;

This finds employees who joined after their managers.

A Self Join can also find related products when a product table contains a related_product_id.

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

The same products table is used for both product and related product.

• Forgetting to use table aliases.
• Using the wrong relationship column.
• Joining the table to itself without a proper ON condition.
• Accidentally producing a large number of rows.
• Forgetting to prevent duplicate pairs when comparing records.
• Using INNER JOIN when unmatched parent records should also be displayed.

A simplified logical processing order is:

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

The table can be referenced multiple times using different aliases during the JOIN operation.

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

This creates a practical employee hierarchy report showing each employee and their manager.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2),
    manager_id INT,
    joining_date DATE
);

INSERT INTO employees
(employee_name, department, salary, manager_id, joining_date)
VALUES
('Raj Kumar', 'Management', 80000, NULL, '2020-01-10'),
('Amit Singh', 'IT', 50000, 1, '2021-03-15'),
('Priya Sharma', 'IT', 55000, 1, '2022-05-20'),
('Rahul Verma', 'IT', 40000, 2, '2023-07-12'),
('Neha Kumari', 'HR', 45000, 1, '2022-08-10'),
('Ravi Kumar', 'IT', 42000, 2, '2024-02-15');

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

-- Employees managed by Raj
SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Raj Kumar';

-- Managers and number of direct employees
SELECT
    m.employee_name AS manager,
    COUNT(e.employee_id) AS total_employees
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.employee_name;

-- Employees earning more than their managers
SELECT
    e.employee_name AS employee,
    e.salary AS employee_salary,
    m.employee_name AS manager,
    m.salary AS manager_salary
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

-- Employees who joined after their managers
SELECT
    e.employee_name AS employee,
    m.employee_name AS manager,
    e.joining_date,
    m.joining_date AS manager_joining_date
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.joining_date > m.joining_date;

This complete example demonstrates employee-manager relationships, LEFT JOIN, INNER JOIN, COUNT(), GROUP BY, salary comparison, and date comparison using a Self Join.