MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

2089
february 23, 2023, at 00:01

MySQL supports several types of joins to combine data from multiple tables. Here are some of the most common join types and how to use them:

INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables. The syntax is as follows:
?
1
2
3
4
SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN

The LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there is no matching row in the right table, the result will contain NULL values. The syntax is as follows:
?
1
2
3
4
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN

The RIGHT JOIN is similar to the LEFT JOIN, but it returns all the rows from the right table and the matched rows from the left table. If there is no matching row in the left table, the result will contain NULL values. The syntax is as follows:
?
1
2
3
4
5
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
<h3>FULL OUTER JOIN</h3>
The FULL OUTER JOIN returns all the rows from both tables, with NULL values in the columns where there is no match. MySQL does not have a built-in FULL OUTER JOIN syntax, but it can be emulated with a UNION of a LEFT JOIN and a RIGHT JOIN. The syntax is as follows:
?
1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
WHERE table1.column IS NULL;
<h3>CROSS JOIN</h3>
The CROSS JOIN returns the Cartesian product of the two tables, which is all possible combinations of rows from both tables. The syntax is as follows:
?
1
2
3
SELECT *
FROM table1
CROSS JOIN table2;
Joins can be useful for combining data from different tables to create a single result set. By understanding the different types of joins, you can select the appropriate one for your specific requirements.