MySQL Self Join: Definition, Syntax, and Examples

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:

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	3

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.

Example

SELECT e1.name AS Employee, e2.name AS Manager

FROM employees e1

LEFT JOIN employees e2

ON e1.manager_id = e2.employee_id;

Try it yourself

Output:

Employee	Manager
Alice	NULL
Bob	Alice
Charlie	Alice
David	Bob
Eve	Charlie

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.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.