MySQL Self Join

If a tables join with itself then we call it self join

There is no special keyword for self-join. You can use the WHERE clause to make a self join

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Example

Employee and Manager relationship is a good example to understand the self join in MySQL

CREATE TABLE `cc_test`.`employees` (
  `employee_id` INT NOT NULL,
  `employee_name` VARCHAR(45) NULL,
  `manager_id` INT NULL,
  PRIMARY KEY (`employee_id`));

SELECT A.employee_name,B.employee_name AS manager FROM employees as A 
LEFT JOIN 
employees as B
ON B.employee_id = A.manager_id