MySQL SELF JOIN Examples

Table Creation and Insertion of Values

We are going to create a table named employees and we are inserting five employees and their details.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES (1, 'John', NULL);
INSERT INTO employees VALUES (2, 'Jane', 1);
INSERT INTO employees VALUES (3, 'Bob', 2);
INSERT INTO employees VALUES (4, 'Alice', 1);
INSERT INTO employees VALUES (5, 'Charlie', 3);

select * from employees;

The above table displays the employees and their details inserted in the table.

Example 1: MySQL SELF JOIN Using INNER JOIN Clause

SELECT e1.employee_id AS employee_id, 
       e1.employee_name AS employee_name, 
       e2.employee_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

Output:

Explanation: In the above example, e1 and e2 are aliases of the same table(employees) and we are joining the columns manager_id and employee_id of the same table using inner join which includes all data that are common from both e1 and e2 aliases. Since we use inner join here, all rows from the employees table with e1 and e2 will be displayed in the result only if they match the condition e1.manager_id = e2.employee_id will be displayed in the result.

Example 2: MySQL SELF JOIN Using LEFT JOIN Clause

SELECT e1.employee_id AS employee_id, 
       e1.employee_name AS employee_name, 
       e2.employee_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Output:

Explanation: In the above example, e1 and e2 are aliases of the same table and we are joining the columns manager_id and employee_id of the same table using left join which includes all data from alias e1. Since we use left join here, all rows from the employees table with e1 will be displayed in the result and only the matching rows from the same table with alias e2 based on the condition e1.manager_id = e2.employee_id will be displayed in the result.

Joins are very important for effective data retrieval and analysis. The ‘JOIN‘ clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and SELF JOIN.

In this article, we’ll explore the concept of MySQL SELF JOIN, using simple examples to show how it works. A SELF JOIN is a type of join where a table is joined with itself, allowing you to compare rows within the same table. We’ll explain how to use MySQL SELF JOIN with both INNER JOIN and LEFT JOIN clauses, highlighting different scenarios where SELF JOIN can be useful. By the end, you’ll understand how to apply SELF JOIN in practical situations to manage and analyze your data effectively.