Different Type of Joins in Mysql | Learning Curve- Perfect Tutorial for the beginers

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. OUTER JOIN(FULL OUTER JOIN)

Example:
——–

Use the below tables

User table:
———–
—————————–
id | name | designationid
—————————–
1 | Arun | 1 |
—————————–
2 | jiju | 1 |
—————————–
3 | Martin | 2 |
—————————–
4 | Manu | 5 |
—————————–
5 | Sreejith| (NULL) |
—————————–

Designation Table
—————–

———————–
id | name |
———————–
1 | police |
———————–
2 | software engr |
———————–
3 | doctor |
———————–
4 | teacher |
———————–
5 | buisnessman |
————————

Innerjoin example:

Inner join will returns all the matching records from both the table user and designation.

SELECT user.name, Designation.name
FROM user
INNER JOIN Designation on user.designationid = Designation.id;

It will show the output as below

LEFT Join:
———-

The LEFT JOIN keyword returns all rows from the left(Users) table1 , with the matching rows in the right(Designation) table2.

SELECT user.name, Designation.name
FROM user
LEFT JOIN Designation ON user.designationid = Designation.id

it will show the output as below

Right Join
———–

The RIGHT JOIN keyword returns all rows from the right(designation) table2 , with the matching rows in the left(Users) table1.

SELECT user.name, Designation.name
FROM user
RIGHT JOIN Designation ON user.designationid = Designation.id

it will show the output as below