SQL-FULL JOIN

<< Previous - SQL RIGHT OUTER Join

Next - SQL SELF Join >>

What is FULL JOIN in SQL?

• Full JOIN is also called FULL OUTER JOIN. FULL JOIN is a combined result set of both left and right join.
• The SQL full join returns all the records from both tables.
• Below is a diagram to explain the FULL JOIN.

  

SQL FULL JOIN - Syntax

SELECT Table1.column-name1 Table1.column-name2  Table2.column-name 2......
FROM Table-name1
FULL JOIN Table-name2
ON Table1.column =  Table2.column;

SQL FULL JOIN example

Consider the demo tables that are given below:

1. Student_Personal Table:

   
   +--------+---------+------+-----------+
   | RollNo | Name    | Age  | City      |
   +--------+---------+------+-----------+
   |      1 | Aruna   |   18 | Chennai   |
   |      2 | Varun   |   19 | Bangalore |
   |      3 | Ara     |   19 | Kerala    |
   |      4 | Markdin |   18 | Mumbai    |
   |      5 | Kannan  |   20 | Kerala    |
   |      6 | Kanika  |   18 | Chennai   |
   |      7 | Jose    |   19 | Kerala    |
   |     10 | Sreeja  |   20 | Bangalore |
   +--------+---------+------+-----------+
   

2. Student_ID Table:

   
   +--------+-------+---------+------+
   | RollNo | ID_NO | Name    | Dept |
   +--------+-------+---------+------+
   |      1 |   101 | Arun    | CSE  |
   |      2 |   102 | Varun   | IT   |
   |      3 |   103 | Ara     | ECE  |
   |      4 |   104 | Markdin | ECE  |
   |      5 |   105 | Kannan  | IT   |
   |      6 |   106 | Aruna   | CSE  |
   |      7 |   107 | Jose    | ECE  |
   |      8 |   108 | Jithu   | CSE  |
   |      9 |   109 | Sai     | ECE  |
   +--------+-------+---------+------+
   

Following is the FULL JOIN query statement used to join these two tables:

mysql> SELECT Student_Personal.Rollno,Student_Personal.Name,Student_ID.ID_NO,Student_ID.Dept
    -> FROM Student_Personal
    -> FULL JOIN Student_ID
    -> ON student_Personal.RollNo = Student_ID.RollNo;

When you execute this above statement, you would get the following result-set

+--------+---------+-------+------+
| Rollno | Name    | ID_NO | Dept |
+--------+---------+-------+------+
|      1 | Aruna   |   101 | CSE  |
|      2 | Varun   |   102 | IT   |
|      3 | Ara     |   103 | ECE  |
|      4 | Markdin |   104 | ECE  |
|      5 | Kannan  |   105 | IT   |
|      6 | Kanika  |   106 | CSE  |
|      7 | Jose    |   107 | ECE  |
|     10 | Sreeja  |  Null | Null |
|   NULL | NULL    |   108 | CSE  |
|   NULL | NULL    |   109 | ECE  |
+--------+---------+-------+------+
10 rows in set (0.00 sec)

<< Previous - SQL RIGHT OUTER Join

Next - SQL SELF Join >>