SELF JOIN is basically used when the table joins itself.
For SELECT Statement
Syntax:-
SELECT table_A.column_name(s), table_B.column_name(s)
FROM table_name as table_A
SELF JOIN
table_name AS table_B
ON table_A.column_name=table_B.column_name
Example:- Suppose we have employees table
employees Table:- this table has the employee’s details and manager_id is an employee_id.
+----+------------+-----------+----------------+-----------+------------+
| id | first_name | last_name | email | country | manager_id |
+----+------------+-----------+----------------+-----------+------------+
| 1 | John | Tailor | john@abc.com | USA | 0 |
| 2 | Rom | Tailor | rom@abc.com | USA | 1 |
| 3 | Andrew | Symonds | andrew@abc.com | Australia | 2 |
| 4 | Miacle | Tailor | miacle@abc.com | Australia | 1 |
| 5 | Sachin | Tendulkar | sachin@abc.com | India | 0 |
| 6 | Virat | Kohli | virat@abc.com | India | 5 |
| 7 | rohit | NULL | rohit@abc.com | India | 5 |
+----+------------+-----------+----------------+-----------+------------+
Find all employees who have manager.
Query:-
SELECT employees_A.id,employees_A.first_name,
employees_A.last_name,
employees_A.email,employees_A.country,
employees_B.first_name AS manager
FROM employees AS employees_A
SELF JOIN
employees AS employees_B
On employees_A.manager_id=employees_B.id
Output:-
+----+------------+-----------+----------------+-----------+---------+
| id | first_name | last_name | email | country | manager |
+----+------------+-----------+----------------+-----------+---------+
| 2 | Rom | Tailor | rom@abc.com | USA | John |
| 3 | Andrew | Symonds | andrew@abc.com | Australia | Rom |
| 4 | Miacle | Tailor | miacle@abc.com | Australia | John |
| 6 | Virat | Kohli | virat@abc.com | India | Sachin |
| 7 | rohit | NULL | rohit@abc.com | India | Sachin |
+----+------------+-----------+----------------+-----------+---------+
5 rows in set (0.00 sec)