It is used in the situation when you want to fetch multiple data from the different tables, and it is used with a select statement.

Types of Joins in MYSQL

Inner join in MYSQL

When the join condition is satisfied, it will fetch all the rows from the different tables.

Syntax:

SELECT columns
FROM table1
IINER JOIN table 2
ON table1.column=table2.column;

Example:

SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;

It is used to return all the rows from the left side of the tables when the condition is satisfied.

Syntax:

SELECT columns
FROM table1
IINNER JOIN table2
ON table1.column=table2.coulmn;

Example:

SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;

It is used when rows from the right-hand side have to be joined with the given condition.

Syntax:

SELECT columns
FROM table 1
RIGHT [OUTER] JOIN table2
ON table1.column=table2.column;

Example:

SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;