SQL SELF JOIN

<< Previous - SQL FULL Join

Next - SQL CROSS 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)

<<Previous - SQL FULL Join

Next - SQL CROSS Join >>