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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
