Self join in mysql employee manager example

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` (
     `id` int NOT NULL AUTO_INCREMENT,
    `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 e.id, e.name , m.name as manager from employees e left join employees m on e.manager_id = m.id;

Output

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