Skip to content

MySQL JOIN

MySQL supports JOIN syntaxes for the part of SELECTstatements and multiple table UPDATE and DELETE statements. JOIN clause helps us, when we manipulate with more than one table. Generally most of the beginners quite confused, while starting with the JOIN clause. But this part explains you in a simple and clear way.

For example,

emp_master

emp_master_id emp_name emp_status
1 Aravind 1
2 Karthik 1
3 Jhon 1
4 Harry 0
5 Stephen 0
6 Santhosh 1
7 Ram 0

emp_slave

emp_slave_id emp_id emp_city
1 1 Chennai
2 2 Banglore
3 3 Mumbai
4 4 Pune
5 5 Delhi
6 8 banglore
7 9 Mumbai

Cartesian Product

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a, emp_slave b

Joins are quite hard concept to grasp for beginning MySQL developers. This is the simple cartesian product where beginners can easily understand. The comma notation is used to produce a cartesian product between two tables, which means produce a matrix of all records from the table “emp_master” and the table “emp_slave”, so two tables with 7 and 7 records respectively produces 49 records.

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a, emp_slave b WHERE 
a.emp_master_id = b.emp_id
emp_master_id emp_name emp_status emp_slave_id emp_city
1 Aravind 1 1 Chennai
2 Karthik 1 2 Banglore
3 Jhon 1 3 Mumbai
4 Harry 0 4 Pune
5 Stephen 0 5 Delhi

Inner Join

This is the most common JOIN used in MySQL. It fetches the records from both the tables that matches with the given criteria.

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a INNER JOIN
emp_slave b ON a.emp_master_id = b.emp_id

(or)

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a JOIN
emp_slave b ON a.emp_master_id = b.emp_id
emp_master_id emp_name emp_status emp_slave_id emp_city
1 Aravind 1 1 Chennai
2 Karthik 1 2 Banglore
3 Jhon 1 3 Mumbai
4 Harry 0 4 Pune
5 Stephen 0 5 Delhi

It returned only the data from the records which have the same value for “emp_master_id” and “emp_id”.

Opposed to INNER JOIN, there is also OUTER JOIN. There are 2 types of OUTER JOINs, they are LEFT and RIGHT JOIN, which are as follows,

Left Join

This brings all requested data from the table that appears to the left of the JOIN, plus the data from the right table which match with the first one.

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a LEFT JOIN
emp_slave b ON a.emp_master_id = b.emp_id
emp_master_id emp_name emp_status emp_slave_id emp_city
1 Aravind 1 1 Chennai
2 Karthik 1 2 Banglore
3 Jhon 1 3 Mumbai
4 Harry 0 4 Pune
5 Stephen 0 5 Delhi
6 Santhosh 1 NULL NULL
7 Ram 0 NULL NULL

Right Join

This brings all requested data from the table that appears to the right of the JOIN, plus the data from the right table which match with the first one.

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a RIGHT JOIN
emp_slave b ON a.emp_master_id = b.emp_id
emp_master_id emp_name emp_status emp_slave_id emp_city
1 Aravind 1 1 Chennai
2 Karthik 1 2 Banglore
3 Jhon 1 3 Mumbai
4 Harry 0 4 Pune
5 Stephen 0 5 Delhi
NULL NULL NULL 6 Banglore
NULL NULL NULL 7 Mumbai

Cross Join

A CROSS JOIN is exactly similar to the Cartesian product.

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a CROSS JOIN emp_slave b

(or)

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a INNER JOIN emp_slave b

(or)

SELECT a.emp_master_id, a.emp_name, a.emp_status, 
b.emp_slave_id, b.emp_city FROM emp_master a, emp_slave b

All the query are similar, whereas it produce a cartesian between two tables i.e. matrix of both the table which creates 49 records.

<< MySQL SELECT

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: