If a Table is joined to itself using one of the join types, then such a type of join is called SELF JOIN.
To demonstrate MySQL SELF JOIN, let us create an Employee table with data as shown in the following image by executing the following script:
-- Create the Demo Database
CREATE DATABASE MySQLSelfJOINDemo;
-- Create EMployee Table
CREATE TABLE Employee
(EmployeeId INT, Name NVARCHAR(50), ManagerId INT);
-- Insert sample records in to the employee table
INSERT INTO Employee
EXAMPLE: Now if we need to get the name of all the Employees with their Manager names, then we have to Join Employee Table to itself as shown in the below script. As both Employee and his/her Manager details are present in the same employee table.
E.Name 'Employee Name', M.Name 'Manager Name'
FROM Employee E
INNER JOIN Employee M
ON M.EmployeeId = E.ManagerId;