Krivalar Tutorials SQL SELF JOIN
What is SELF JOIN in SQL?
- SQL SELF JOIN uses to connect the table with itself.
- Usually, we can apply the join concept with two or more tables.
- For performing self-join, we can create two aliases(t1 and t2) for a single table(T). Each Alias is considered a separate copy of the table.
- In SQL self joining, each row in alias table t1 is compared with each row in alias table t2 and returns the resulting table.
- The resulting table will contain a set of rows if they all meet the specified condition.
SQL SELF JOIN - Syntax
SELECT T1.column-name1, T1.column-name2, T2.column-name1, T2.column-name2......
FROM Table-name1 T1,Table-name1 T2
WHERE T1.common_column-name = T2.common_column-name;
[OR]
SELECT T1.column-name1, T1.column-name2, T2.column-name1, T2.column-name2......
FROM Table-name1 T1
JOIN Table-name1 T2
ON T1.common_column-name = T2.common_column-name;
Example with SELF JOIN
Consider the below demo Staff_Table table for understanding the SELF JOIN.
+-------+-----------+-------+--------+--------+
| ID_NO | Name | Dept | Salary | Job_ID |
+-------+-----------+-------+--------+--------+
| 101 | Madhan | IT | 20000 | 102 |
| 102 | Kamini | IT | 25000 | NULL |
| 103 | Senthil | CSE | 30000 | 104 |
| 104 | Vani | CSE | 30000 | NULL |
| 105 | Aadhi | MECH | 35000 | NULL |
| 106 | Arun | CIVIL | 35000 | NULL |
| 107 | Vasnthi | CIVIL | 28000 | 106 |
| 108 | Balan | CIVIL | 27500 | 106 |
| 109 | Devid | MECH | 23500 | 105 |
| 110 | Aruna | ADMIN | 15000 | 113 |
| 111 | Boomi | ADMIN | 17000 | 113 |
| 112 | Kathirvel | ADMIN | 17500 | 113 |
| 113 | Kathir | ADMIN | 22000 | NULL |
+-------+-----------+-------+--------+--------+
mysql> select a.ID_NO, a.Name,a.Dept,b.name as HOD
-> from Staff_Table a, staff_table b
-> where a.job_ID = b.ID_NO;
[OR]
mysql> select a.ID_NO, a.Name,a.Dept,b.name as HOD
-> from Staff_Table a
-> join staff_table b
-> on a.Job_ID = b.ID_NO;
The above SQL query would produce the following result.
+-------+-----------+-------+--------+
| ID_NO | Name | Dept | HOD |
+-------+-----------+-------+--------+
| 101 | Madhan | IT | Kamini |
| 103 | Senthil | CSE | Vani |
| 109 | Devid | MECH | Aadhi |
| 108 | Balan | CIVIL | Arun |
| 107 | Vasanthi | CIVIL | Arun |
| 112 | Kathirvel | ADMIN | Kathir |
| 111 | Boomi | ADMIN | Kathir |
| 110 | Aruna | ADMIN | Kathir |
+-------+-----------+-------+--------+
8 rows in set (0.01 sec)
