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','[email protected]');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','[email protected]');
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)

Mysql Inner Join

Mysql Inner Join

Mysql Inner Join

Tutorials