SQL SELF JOIN

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)

SQL SELF JOIN – Interview Questions

SQL SELF JOIN – Objective Questions (MCQs)

Related SQL SELF JOIN Topics