Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Sqljoin Mysql Outer Join

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Outer Join

Advertisement
Mysql Outer Join return you the set of all matching records from both table. The Outer Join does not requires each records to be matched in both the tables.

Mysql Outer Join

     

Mysql Outer Join return you the set of all matching records from both table. The Outer Join does not requires each records to be matched in both the tables.

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:-

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)

Query to view data of  Table named roseindia:-

mysql> select * from roseindia;

Output:-

+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | Nainital |
|     2 | Komal     | Merrut   |
|     3 | Amit      | Lucknow  |
|     4 | Sandeep   | Lucknow  |
+-------+-----------+----------+
4 rows in set (0.00 sec)

Query to Create Table newstrack:-

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:-

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)

Query to view data of  Table named newstrack:-

mysql> select * from newstrack;

Output:-

+-------+-----------+------------------+
| Empid | firstname | email            |
+-------+-----------+------------------+
|     1 | Suman     | girish.gmail.com |
|     2 | Ravi      | komal.gmail.com  |
|     3 | Santosh   | Amit.gmail.com   |
+-------+-----------+------------------+
3 rows in set (0.00 sec)

Left Outer Join:-

Query to join the data of the above two tables named roseindia and newstrack using left outer join:-

The Left Outer Join return you the set of records from 'newstrack' matches with 'roseindia' on the basis of common column EmpId from both tables. Incase there is no matching records in 'newstrack' with 'roseindia'. The 'Null' would be specified there.

mysql> SELECT * FROM roseindia AS R
    ->      LEFT JOIN newstrack AS 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             |
+-------+-----------+----------+-------+-----------+------------------+
4 rows in set (0.00 sec)

Right Outer Join:-

This join return you all the records from left table matches with right table.

Query to join the data of the above two tables named roseindia and newstrack using right outer join:-

The Query return you the set of records from 'roseindia' matches with 'newstrack' on the basis of common column emp id from both tables. The records  in the left table which are  unmatched with the right table are specified with null value. 

mysql> select * from roseindia as R
    ->     right join newstrack as 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   |
+-------+-----------+----------+-------+-----------+------------------+
3 rows in set (0.00 sec)
Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



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

Related Tutorials

Discuss: Mysql Outer Join  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:2
deepak singh
April 6, 2011
mysql query

whenever we are creating a view table and by view we are searching the data from three tables by using outer join please let me know all these related examples?
Deepanjan
September 14, 2011
MYSQL

Thanks
DMCA.com