INNER JOIN
The INNER JOIN (or just JOIN) returns only the rows that have matching values in both tables being joined. It combines rows from two or more tables where the join condition is satisfied.
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
The result set includes only the rows where the join condition is met (matching values in both tables).
LEFT JOIN
The LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, NULL values are returned for the right table columns.
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
The result set includes:
- all rows from the left table and
- the matching rows from the right table.
If there are no matches in the right table, NULL values are included for the right table columns.
RIGHT JOIN
The RIGHT JOIN returns all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, NULL values are returned for the left table columns.
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
The result set includes:
- all rows from the right table and
- the matching rows from the left table.
If there are no matches in the left table, NULL values are included for the left table columns.
These different types of JOINs offer flexibility in combining data from multiple tables based on specific requirements. By understanding the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN, you can effectively retrieve and combine data from multiple tables to get the desired result set.