What is SELF JOIN in MySQL ?

A self JOIN is a regular join, but the table is joined with itself.

Syntax of MySQL SELF JOIN

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

T1 and T2 are different table aliases for the same table.

Example of MYSQL SELF JOIN

Employee Table

+------------+-----------+----------+--------------+
| EmployeeID | FirstName | LastName | SupervisorID |
+------------+-----------+----------+--------------+
|          1 | Tarun     | Goswami  | 2            |
|          2 | Ram       | Sharma   | NULL         |
|          3 | Rohit     | Verma    | 1            |
|          4 | Ronit     | Roy      | 2            |
|          5 | Bhanu     | Bhardwaj | 1            |
|          6 | Sunil     | Sharma   | 1            |
+------------+-----------+----------+--------------+

MYSQL SELF JOIN Query

Suppose you want to find supervisor of all employee.

use Company;
SELECT E1.EmployeeID, 
CONCAT(E1.FirstName, ' ', E1.LastName) As Name, 
E1.SupervisorID, 
CONCAT(E2.FirstName, ' ', E2.LastName) as SupervisoName
FROM Employee E1
LEFT OUTER JOIN Employee E2 on E1.SupervisorID = E2.EmployeeID

Output

+------------+----------------+--------------+---------------+
| EmployeeID | Name           | SupervisorID | SupervisoName |
+------------+----------------+--------------+---------------+
|          1 | Tarun Goswami  | 2            | Ram Sharma    |
|          2 | Ram Sharma     | NULL         | NULL          |
|          3 | Rohit Verma    | 1            | Tarun Goswami |
|          4 | Ronit Roy      | 2            | Ram Sharma    |
|          5 | Bhanu Bhardwaj | 1            | Tarun Goswami |
|          6 | Sunil Sharma   | 1            | Tarun Goswami |
+------------+----------------+--------------+---------------+