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 |
+------------+----------------+--------------+---------------+