Home Sql Sqljoin Mysql Full Join
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Full Join

Advertisement
Mysql Full Join is used to return all the records from both left and right outer join.

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','girish@gmail.com');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','komal@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','Amit@gmail.com');
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)
Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 16, 2009

Ask Questions?    Discuss: Mysql Full Join  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Ayaz
November 10, 2011
ayaz

Thank you, this is help full. please make it simple for beginner's. Regards, Ayaz
DMCA.com