In MySQL join is used to combine records from 2 or more tables based on a common column between them.

INNER JOIN: It returns all rows when there is a match in both tables.

LEFT JOIN: It returns all rows from the left table and matched rows from the right table.The result is NULL in the right records for non matched rows.

RIGHT JOIN: It returns all rows from the right table and the matched rows from the left table.The result is NULL in the left records for non matched rows.

FULL JOIN: It returns all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

We have two tables with the following values.

user

id  first_name       last_name
.......................................
1   John                Simp                 
2   Chris               Hely                   
3   Joy                 Roy                      
4   Jenny               Mill 



user_address

user_id  Place
.........................
1           Canada
2           Ottawa
3           London
5           India

INNER JOIN

It gives the rows which are common in user and user_address

syntax is :

SELECT column_names
FROM table1
INNER JOIN table2
ON table1.common_name = table2.common_name;

Now write the query for above two tables :

SELECT u.first_name,u.last_name,ua.place
FROM user u
INNER JOIN user_address ua
ON u.id = ua.user_id;

Result:

first_name      last_name      Place
..............................................
John            Simp           Canada
Chris           Hely           Ottawa
Joy             Roy            London

LEFT JOIN

It will give all selected rows from table user and matched rows form user_address table. It will put NULL for non matched rows in user_address table..

Syantax

SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.common_name = table2.common_name;

Now write the query for above two tables :

SELECT u.first_name,u.last_name,ua.place
FROM user u
LEFT JOIN user_address ua
ON u.id = ua.user_id;

Result:

first_name      last_name      Place
..............................................
John            Simp           Canada
Chris           Hely           Ottawa
Joy             Roy            London
Jenny           Mill           NULL

RIGHT JOIN

It will give all the selected rows from table user_address and matched rows form user table. It will put NULL for non matched rows in user table..

Syantax

SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.common_name = table2.common_name;

Now write the query for above two tables :

SELECT u.first_name,u.last_name,ua.place
FROM user u
RIGHT JOIN user_address ua
ON u.id = ua.user_id;

Result:

first_name      last_name      Place
..............................................
John            Simp           Canada
Chris           Hely           Ottawa
Joy             Roy            London
NULL            NULL           India

FULL JOIN

It works same as the UNION operation. It will return all the selected rows from both tables.

Syantax

SELECT column_names
FROM table1
FULL JOIN table2
ON table1.common_name = table2.common_name;

Now write the query for above two tables :

SELECT u.first_name,u.last_name,ua.place
FROM user u
FULL JOIN user_address ua
ON u.id = ua.user_id;

Result:

first_name      last_name      Place
..............................................
John            Simp           Canada
Chris           Hely           Ottawa
Joy             Roy            London
Jenny           Mill           NULL
NULL            NULL           India

Hope this will help you :)