Mysql Join Query
Mysql Join Query is used to return the matchable records from both
table on the basis of common column.
Understand with Example
The Tutorial illustrate an example from Mysql Join Query. To grasp this we
create a table roseindia with fieldname and data type.
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 statement add 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 we create another table 'newstrack'. The
create table 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)
|
Query to join data of Table's created above:-
The given below code uses join query that return you
the matches records from table 'roseindia' and 'newstrack' on the basis of
common column Empid on respective tables.
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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
