Home Sql Sqljoin Mysql Outer Join

Mysql Outer Join
Posted on: January 16, 2009 at 12:00 AM

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)

Related Tags for Mysql Outer Join:
sql, mysql, c, table, ui, join, tables, return, set, tab, record, matching, match, records, to, each, require, bot, e, not, from, in, no, m, out, j, es, all, outer, do, s, urn, at, my, ir, ll, ea, rds, s, s, rd, th, chi, ab, abl, om, o

«Previous Index Next»