MySQL Self Join

MySQL SELF JOIN

A SELF JOIN in MySQL is a type of join where a table is joined with itself.

• It is useful when you need to compare rows within the same table.
• Typically used for hierarchical data (e.g., employees and managers) or for finding relationships between rows in a single table.
• You use table aliases to differentiate between the two instances of the table.

Self Join Syntax

SELECT A.column_name, B.column_name
FROM table_name A
JOIN table_name B
ON A.common_column = B.common_column;

• A and B → Aliases for the same table to treat them as two separate tables.
• ON → Specifies the condition for matching rows.

Example Scenario: Employee-Manager Relationship

Consider an Employees table where each employee has a manager.

The table has a manager_id column that refers to the employee_id of their manager.

Employees Table:

• Alice has no manager (CEO).
• Bob and Charlie report to Alice.
• David reports to Bob.
• Eve reports to Charlie.

Example 1: Display Employee Names with Their Manager Names

The following SQL statement display employee names with their manager names.

Output:

Explanation:

• LEFT JOIN ensures that employees without a manager (like Alice) are still displayed with NULL as the manager.
• e1 represents the employee and e2 represents their manager.