Self Join in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Self-Join in MySQL with Examples

In this article, I am going to discuss the Self Join in MySQL with Examples. Please read our previous article where we discussed Right Outer Join in MySQL with Examples. At the end of this article, you will understand what is Self-Join and when and how to use Self Join in MySQL.

What is Self-Join in MySQL?

The Self Join in MySQL is nothing but joining a table by itself. We need to use Self Join in MySQL when we have some relations between the columns of the same table. If this is not clear at the moment then don’t worry, we will discuss this with an example.

When you are implementing the self-join mechanism in MySQL, then you have to create the alias for the table name. You can create any number of aliases for a single table name in MySQL. Aliases are nothing but the alternative name given to a table. The Self Join is not a different kind of join. The Self Join is like any other join except that the two instances of the same table will be joined. So, it can be classified as any type of join, such as

1. Inner Join
2. Outer (Left, Right, Full) join
3. Cross Join

Understanding Self Join in MySQL with Examples

We are going to use the following Employee table to understand the Self Join concept in MySQL.

Please use the following SQL script to create the database company and Employee table and populate the Employee table with the required test data.

CREATE DATABASE Company;
USE Company;

-- Create the Employee table
CREATE TABLE Employee
(
  EmployeeID INT PRIMARY KEY,
  FullName VARCHAR(50),
  Gender VARCHAR(50),
  ManagerID INT
);

-- Insert the following records
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male', 3);
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female', 1);
INSERT INTO Employee VALUES(3, 'Preety', 'Female', NULL);
INSERT INTO Employee VALUES(4, 'Anurag', 'Male', 1);
INSERT INTO Employee VALUES(5, 'Sambit', 'Male', 1);
INSERT INTO Employee VALUES(6, 'Rajesh', 'Male', 3);
INSERT INTO Employee VALUES(7, 'Hina', 'Female', 3);

The Employee table contains information for both normal employees as well as managers of that employee which is the ManagerId. So, in order to find out the managers of each employee, we need a Self-Join because both Manager and Employee exist in the same table.

Left Self-Join Example:

A MANAGER is also an EMPLOYEE. Both the NORMAL EMPLOYEE and MANAGER records are present in the same Employee table. So, here we need to join the table Employee with itself using different alias names, let say, E for Employee and M for Manager. Here we are going to use the Left Outer Join which will get the records with ManagerId NULL. You can see in the output that Preety’s record is also retrieved, but the MANAGER is NULL.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
LEFT JOIN Employee M
ON E.ManagerId = M.EmployeeId;

Once you execute the above query, you will get the following output.

Inner Self Join Example:

Now, we are going to use the Inner Self Join to join two instances of the same Employee table which will not retrieve the records with ManagerId NULL. You can see in the output that Preety’s record is not retrieved.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
INNER JOIN Employee M
ON E.ManagerId = M.EmployeeId;

Once you execute the above SQL Query, you will get the below result set.

Cross Self Join Example in MySQL:

Now, we are going to use the Cross Self Join to join two instances of the same Employee table. In this case, each record of instance will be multiplied with each other of other instances.

SELECT E.FullName as Employee, M.FullName as Manager
FROM Employee E
CROSS JOIN Employee M;

Here, in this article, I try to explain the Self Join in MySQL with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.