Mysql Join Types
The Tutorial help you to understand an example on different types of join. To grasp the different type of join, we create a table 'roseindia'. The create table statement is used to create a table 'roseindia'.
Query to Create Table named roseindia:-
mysql> create table roseindia(Empid int,firstname varchar(30)); Query OK, 0 rows affected (0.05 sec)
Query to insert data into Table named roseindia:-
The insertinto add the records or rows into the table 'roseindia'.
mysql> insert into roseindia values(01,'Girish'); Query OK, 1 row affected (0.02 sec) mysql> insert into roseindia values(02,'Komal'); Query OK, 1 row affected (0.00 sec) mysql> insert into roseindia values(03,'Amit'); Query OK, 1 row affected (0.02 sec) mysql> insert into roseindia values(04,'Sandeep'); Query OK, 1 row affected (0.03 sec)
Query to view data of Table named roseindia:-
To view the output from table 'roseindia' we use select query that returns you the records from table 'roseindia'.
mysql> select * from roseindia;
Output:-
+-------+-----------+ | Empid | firstname | +-------+-----------+ | 1 | Girish | | 2 | Komal | | 3 | Amit | | 4 | Sandeep | +-------+-----------+ 4 rows in set (0.00 sec)
Query to Create Table newstrack:-
The Query create table create another table newstrack.
mysql> create table newstrack(Empid int, lastname 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'); Query OK, 1 row affected (0.02 sec) mysql> insert into newstrack values(02,'Ravi'); Query OK, 1 row affected (0.01 sec) mysql> insert into newstrack values(03,'Santosh'); Query OK, 1 row affected (0.01 sec) mysql> insert into newstrack values(04,'Vinod'); Query OK, 1 row affected (0.03 sec)
Query to view data of Table named newstrack:-
mysql> select * from newstrack;
Output:-
+-------+-----------+ | Empid | firstname | +-------+-----------+ | 1 | Suman | | 2 | Ravi | | 3 | Santosh | | 4 | Vinod | +-------+-----------+ 4 rows in set (0.00 sec)
CROSS JOIN:-This type of join is the simplest join. The cross join result in cartesian product of all the records from two tables.
Query to join the data of the above two tables named roseindia and newstrack using crossjoin:-
The below query return you the set of all records or rows from both the table.
mysql> select * from roseindia cross join newstrack order by roseindia.empid;
Output:-
+-------+-----------+-------+-----------+ | Empid | firstname | Empid | firstname | +-------+-----------+-------+-----------+ | 1 | Girish | 1 | Suman | | 1 | Girish | 4 | Vinod | | 1 | Girish | 3 | Santosh | | 1 | Girish | 2 | Ravi | | 2 | Komal | 2 | Ravi | | 2 | Komal | 1 | Suman | | 2 | Komal | 4 | Vinod | | 2 | Komal | 3 | Santosh | | 3 | Amit | 3 | Santosh | | 3 | Amit | 2 | Ravi | | 3 | Amit | 1 | Suman | | 3 | Amit | 4 | Vinod | | 4 | Sandeep | 4 | Vinod | | 4 | Sandeep | 3 | Santosh | | 4 | Sandeep | 2 | Ravi | | 4 | Sandeep | 1 | Suman | +-------+-----------+-------+-----------+ 16 rows in set (0.00 sec)
Before using inner join here we are adding one column named city in the table roseindia
Query:-
To add a new column 'city' we use alter query to modify the table roseindia.
mysql> alter table roseindia add column city varchar(30);
Output:-
mysql> select * from roseindia; +-------+-----------+----------+ | Empid | firstname | city | +-------+-----------+----------+ | 1 | Girish | NULL | | 2 | Komal | NULL | | 3 | Amit | NULL | | 4 | Sandeep | NULL | +-------+-----------+----------+ 4 rows in set (0.00 sec)
Update the table roseindia:-
Query:-
mysql> update roseindia set city = 'Nainital' where empid=1; Query OK, 1 row affected (0.05 sec)
mysql> update roseindia set city = 'Merrut' where empid=1; Query OK, 1 row affected (0.01 sec)
mysql> update roseindia set city = 'Lucknow' where empid=3; Query OK, 1 row affected (0.00 sec)
mysql> update roseindia set city = 'Lucknow' where empid=4; Query OK, 1 row affected (0.02 sec)
Viewing table after updating table:-
Query:-
mysql> select * from roseindia; +-------+-----------+----------+ | Empid | firstname | city | +-------+-----------+----------+ | 1 | Girish | Nainital | | 2 | Komal | Merrut | | 3 | Amit | Lucknow | | 4 | Sandeep | Lucknow | +-------+-----------+----------+ 4 rows in set (0.00 sec)
INNER JOIN OR EQUI JOIN:-This is the type of join where tables are combined based on a common column.
Query to join the data of the above two tables named roseindia and newstrack using inner join:-
The below inner join Query return you only the matchable records from both table on the basis of common column.
mysql> select roseindia.firstname,roseindia.city,newstrack.firstname
-> from roseindia,newstrack where roseindia.empid=newstrack.empid;
Output:-
+-----------+----------+-----------+ | firstname | city | firstname | +-----------+----------+-----------+ | Girish | Nainital | Suman | | Komal | Merrut | Ravi | | Amit | Lucknow | Santosh | | Sandeep | Lucknow | Vinod | +-----------+----------+-----------+ 4 rows in set (0.02 sec)
Altering table newstrack:- 0
mysql> alter table newstrack add column email varchar(30); Query OK, 4 rows affected (0.14 sec) Records: 4 Duplicates: 0 Warnings: 0
Update the table newstrack:-
Query:-
mysql> update newstrack set email='girish.gmail.com' where firstname='suman';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update newstrack set email='komal.gmail.com' where firstname='Ravi';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update newstrack set email='Amit.gmail.com' where firstname='santosh';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update newstrack set email='Sandeep.gmail.com' where firstname='vinod';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from newstrack where firstname='vinod';
Query OK, 1 row affected (0.01 sec)
Viewing table after updating table:- 1
Query:-
ysql> select * from newstrack; +-------+-----------+------------------+ | Empid | firstname | email | +-------+-----------+------------------+ | 1 | Suman | girish.gmail.com | | 2 | Ravi | komal.gmail.com | | 3 | Santosh | Amit.gmail.com | +-------+-----------+------------------+ 3 rows in set (0.02 sec)
OUTER JOIN:- Join is used to combine all rows of one table with matching rows from the other table and also show unmatchable records from other table. It is used whenever multiple tables must be accessed through a SQL SELECT statement
Left Outer Join:-
Query to join the data of the above two tables named roseindia and newstrack using left outer join:- 2
The below Query return you the set of records or rows from table rose india matches with table newstrack. The table also display the set of records from left table that do not matches with the right table. The unmatchable records are displayed with null values.
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 rows that have matching data in the right table 3
Query to join the data of the above two tables named roseindia and newstrack using right outer join:-
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)