Mysql Left Join
Mysql Left Join return you the set of records of right table matches with the
left table. The unmatchable records from right table with the left table are
specified by null values.
Understand with Example
The Tutorial illustrate an example from Mysql Left Join. To understand this example, we
create a table roseindia defined with required fieldname and data type
respectively.
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 adds the records or rows into
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)
|
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:-
Now create another table 'newstrack'.The create
table statement is used 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 Join:-
Query to join the data of the above two tables named
roseindia and newstrack using left outer join:-
The below query return you the set of records from
newstrack matches with the left table 'roseindia'. The query also display
unmatchable records from table 'newstrack' with table 'roseindia' specified with
null.
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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
