Mysql Inner Join
Mysql Inner Join returns the set of only those records which matches in
one table with another.
Understand with Example
The Tutorial illustrate an example from 'Mysql Inner Join'. To grasp this
example we create a table 'roseindia'. The create table is used to create
table roseindia with 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 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:-
To view the detail from table 'roseindia' we use select query to
display the records from given table'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'.
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:-
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)
|
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
using inner join:
The Query below return you the set of only those records
matches in both tables.
mysql> select roseindia.firstname,roseindia.city,newstrack.firstname,newstrack.email
-> from roseindia,newstrack where roseindia.empid=newstrack.empid;
|
Output:-
+-----------+----------+-----------+------------------+
| firstname | city | firstname | email |
+-----------+----------+-----------+------------------+
| Girish | Nainital | Suman | girish.gmail.com |
| Komal | Merrut | Ravi | komal.gmail.com |
| Amit | Lucknow | Santosh | Amit.gmail.com |
+-----------+----------+-----------+------------------+
3 rows in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
