The join clause allows us to combine SQL tables. Tables can be combined in different ways. Think on inner join as an intersection of two tables and an outer join as a union of two tables. This table explains different types of joins.
We will be using the following two tables in the examples:
mysql> select * from tablea;
+----+----------+
| id | value |
+----+----------+
| 3 | GMC |
| 4 | Nissan |
| 5 | Hyundai |
| 6 | Ford |
| 7 | Honda |
| 8 | Peugeot |
| 9 | Suzuki |
| 10 | Mercedes |
| 11 | Audi |
+----+----------+
9 rows in set (0.00 sec)
mysql> select * from tableb;
+----+------------+
| id | value |
+----+------------+
| 1 | Corolla |
| 2 | Jetta |
| 3 | Sierra |
| 4 | Pathfinder |
| 5 | Sonata |
| 6 | Focus |
| 7 | CRV |
| 8 | 307 |
| 9 | Vitarra |
+----+------------+
9 rows in set (0.00 sec)
Inner Join
An inner join is an intersection of two tables. We are intersecting on id so this join will contain all rows where the same id is present in both tables. Note that all row except 1, 2, 10, and 11 are present.
mysql>
select a.id, b.id, a.value, b.value
from tablea a inner join tableb b on a.id = b.id;
+----+----+---------+------------+
| id | id | value | value |
+----+----+---------+------------+
| 3 | 3 | GMC | Sierra |
| 4 | 4 | Nissan | Pathfinder |
| 5 | 5 | Hyundai | Sonata |
| 6 | 6 | Ford | Focus |
| 7 | 7 | Honda | CRV |
| 8 | 8 | Peugeot | 307 |
| 9 | 9 | Suzuki | Vitarra |
+----+----+---------+------------+
7 rows in set (0.00 sec)
Left Join
In a left join, we list all the rows of the left table and corresponding values from the right table. If a the right table does not contain a corresponding row, the column values are replaced with NULL.
mysql>
select a.id, b.id, a.value, b.value
from tablea a left join tableb b on a.id = b.id;
+----+------+----------+------------+
| id | id | value | value |
+----+------+----------+------------+
| 3 | 3 | GMC | Sierra |
| 4 | 4 | Nissan | Pathfinder |
| 5 | 5 | Hyundai | Sonata |
| 6 | 6 | Ford | Focus |
| 7 | 7 | Honda | CRV |
| 8 | 8 | Peugeot | 307 |
| 9 | 9 | Suzuki | Vitarra |
| 10 | NULL | Mercedes | NULL |
| 11 | NULL | Audi | NULL |
+----+------+----------+------------+
9 rows in set (0.00 sec)
Right Join
In a right join, we list all the rows of the right table and corresponding values from the left table. If a the left table does not contain a corresponding row, the column values are replaced with NULL.
mysql> select a.id, b.id, a.value, b.value from tablea a right join tableb b on a.id = b.id;
+------+----+---------+------------+
| id | id | value | value |
+------+----+---------+------------+
| NULL | 1 | NULL | Corolla |
| NULL | 2 | NULL | Jetta |
| 3 | 3 | GMC | Sierra |
| 4 | 4 | Nissan | Pathfinder |
| 5 | 5 | Hyundai | Sonata |
| 6 | 6 | Ford | Focus |
| 7 | 7 | Honda | CRV |
| 8 | 8 | Peugeot | 307 |
| 9 | 9 | Suzuki | Vitarra |
+------+----+---------+------------+
9 rows in set (0.00 sec)
Outer Join
Outer join is a union of two tables. MySQL does not have a full join or outer join clause, so we union two joins to get an outer join.
(select * from tablea left join tableb on tablea.id = tableb.id)
union
(select * from tablea right join tableb on tablea.id = tableb.id);
+------+----------+------+------------+
| id | value | id | value |
+------+----------+------+------------+
| 3 | GMC | 3 | Sierra |
| 4 | Nissan | 4 | Pathfinder |
| 5 | Hyundai | 5 | Sonata |
| 6 | Ford | 6 | Focus |
| 7 | Honda | 7 | CRV |
| 8 | Peugeot | 8 | 307 |
| 9 | Suzuki | 9 | Vitarra |
| 10 | Mercedes | NULL | NULL |
| 11 | Audi | NULL | NULL |
| NULL | NULL | 1 | Corolla |
| NULL | NULL | 2 | Jetta |
+------+----------+------+------------+
11 rows in set (0.00 sec)