Self join in mysql employee manager example

Created at 22-Feb-2024 , By samar

In this blog post, we will learn self join of MySQL using the employee and manager example. In other words, A self join involves using a single table twice in a query, with each instance assigned a different alias. This enables comparisons or relationships between different rows within the same table.

In table we will create a column manager_id which specifies the manager of an employee and we will compare the employees manager_id column with id column of another instance of employee table.

Create employees table

CREATE TABLE `employees` (
    `name` varchar(100) DEFAULT NULL,
    `manager_id` int DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `manager_id` (`manager_id`),
    CONSTRAINT `employees_ibfk_1` FOREIGN KEY (manager_id) REFERENCES employees(id)

Insert data into employees table

insert into employees (name, manager_id) values('John', null), ('Diana', 1), ('David', 1);

Output :

Query OK, 3 rows affected (0.38 sec)
Records: 3  Duplicates: 0  Warnings: 0

Self Join Statment to get Employees and their manager name

select, , as manager from employees e left join employees m on e.manager_id =;


| id | name  | manager |
|  1 | John  | NULL    |
|  2 | Diana | John    |
|  3 | David | John    |
3 rows in set (0.00 sec)

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Buy Me A Coffee

Don't forget to share this article! Help us spread the word by clicking the share button below.

We appreciate your support and are committed to providing you valuable and informative content.

We are thankful for your never ending support.