Mysql Full Join

Mysql Full Join is used to return all the records from both left and right outer join. The joined table contain all records from both tables and fill nulls values for those missing matches on either side. The Full Join in Mysql is the outcome result set of left outer join and right outer join using UNION clause.

Understand with Example

The Tutorial illustrate an example from 'Full Join'. To understand this example, we create a table 'roseindia'. The createtable create a table rose india with required columns and its data type as per required.

Query to Create Table named roseindia:-

mysql> CREATE TABLE roseindia (
            -> Empid int(11),          
             ->firstname varchar(30),  
            -> city varchar(30)        
          -> );
Query OK, 0 rows affected (0.05 sec)

Query to insert data into Table named roseindia:-

The insert into add the records or rows to the table roseindia.

mysql>  insert into roseindia values(01,'Girish','Nainital');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(02,'Komal','Merrut');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into roseindia values(03,'Amit','Lucknow');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(04,'Sandeep','Lucknow');
Query OK, 1 row affected (0.03 sec)

mysql>  insert into roseindia values(06,'AA','Delhi');
Query OK, 1 row affected (0.03 sec)

mysql>  insert into roseindia values(09,'BA','Jaipur');
Query OK, 1 row affected (0.03 sec)

Query to view data of Table named roseindia:-

To view the records from roseindia we use select Query to retrieve the records detail from table roseindia.

mysql> select * from roseindia;

Output:-

mysql> select * from roseindia;
+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | Nainital |
|     2 | Komal     | Merrut   |
|     3 | Amit      | Lucknow  |
|     4 | Sandeep   | Lucknow  |
|     6 | AA        | Delhi    |
|     9 | BA        | Jaipur   |
+-------+-----------+----------+
6 rows in set (0.00 sec)

Query to Create Table newstrack:-

Again, we use create table newstrack that construct a table newstrack with field names and data type.

mysql> CREATE TABLE newstrack (
        ->     Empid int(11),          
         ->    firstname varchar(10),  
         ->    email varchar(30) 
          -> );
Query OK, 0 rows affected (0.03 sec)

Query to insert data into Table named newstrack:-

The insert into add the records or rows into the table newstrack.

mysql>  insert into newstrack values(01,'Suman','[email protected]');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into roseindia values(11,'A','Jai');
Query OK, 1 row affected (0.03 sec)

mysql>  insert into roseindia values(19,'B','Ji');
Query OK, 1 row affected (0.03 sec)

Query to view data of Table named newstrack:-

To view the records of table newstrack we run select keywords that returns you the records of table newstrack.

mysql> select * from newstrack;

Output:-

mysql> select * from newstrack;
+-------+-----------+------------------+
| Empid | firstname | email            |
+-------+-----------+------------------+
|     1 | Suman     | girish.gmail.com |
|     2 | Ravi      | komal.gmail.com  |
|     3 | Santosh   | Amit.gmail.com   |
|    11 | A         | Jai              |
|    19 | B         | Ji               |
+-------+-----------+------------------+
5 rows in set (0.00 sec)

Query to join data of Table's created above using Full join:-

Now we want to join both the table 'roseindia' and 'newstrack' on the basis of full join. The Full Join return you the combine result set of both table and return a null value for missing matches on both side of the table. For example, this will helps you to see each records from roseindia who is in newtrack,but also help you to see each records from roseindia which is not a part of a newstrack. and each newstrack which does not have an roseindia. The UNION clause returns the results of two SQL queries into a single result set table..

mysql>  select * from Roseindia as R
    -> left outer join newstrack as N
    -> on
    -> R.empid=n.empid
    -> Union
    -> select * from Roseindia as R
    -> right outer join newstrack N
    -> on
    -> R.empid=n.empid;

Output:-

+-------+-----------+----------+-------+-----------+------------------+
| Empid | firstname | city     | Empid | firstname | email            |
+-------+-----------+----------+-------+-----------+------------------+
|     1 | Girish    | Nainital |     1 | Suman     | girish.gmail.com |
|     2 | Komal     | Merrut   |     2 | Ravi      | komal.gmail.com  |
|     3 | Amit      | Lucknow  |     3 | Santosh   | Amit.gmail.com   |
|     4 | Sandeep   | Lucknow  |  NULL | NULL      | NULL             |
|     6 | AA        | Delhi    |  NULL | NULL      | NULL             |
|     9 | BA        | Jaipur   |  NULL | NULL      | NULL             |
|  NULL | NULL      | NULL     |    11 | A         | Jai              |
|  NULL | NULL      | NULL     |    19 | B         | Ji               |
+-------+-----------+----------+-------+-----------+------------------+
8 rows in set (0.00 sec)

Mysql Full Join

Mysql Full Join

Mysql Full Join

Tutorials