MySQL SELF JOIN

A SELF JOIN is a join in which a table is joined with itself. It is useful for comparing rows within the same table, such as finding employees and their managers.

• Joins a table with itself.
• Uses aliases to treat the same table as two separate tables.
• Helps compare rows within the same table.
• Commonly used to represent hierarchical relationships.

Syntax

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

Example

Consider the following employees table:

Here, manager_id stores the employee_id of the employee's manager.

To display employees along with their managers:

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;

Output:

Explanation: The employees table is joined with itself. The first instance (e) represents the employee, while the second instance (m) represents the manager. The manager_id is matched with employee_id to find each employee's manager.