In this article, we will show you how to query data in two or more tables using MySQL JOIN.
MySQL JOIN can be roughly divided into four categories:
- INNER JOIN
Gets the record of the intersection of two tables. - LEFT JOIN
Gets all records from the LEFT table, even if the right table has no matching records. - RIGHT JOIN
As opposed to a LEFT JOIN, it is used to get all the records in the RIGHT table, even if there are no matching records in the LEFT table. - FULL JOIN
MySQL does not support FULL JOIN. We can get equivalent results by combining LEFT JOIN, UNION and RIGHT JOIN.
Syntax
select
*
tb1 {LEFT|RIGHT} [OUTER] JOIN tb2
ON tb1.column = tb2.column | USING (column)Examples
Here we have two tables tb1 and tb2:
mysql> select * from tb1;
+----+----------+--------+------+
| id | name | status | age |
+----+----------+--------+------+
| 1 | ylspirit | 1 | 22 |
| 2 | carol | 1 | 21 |
| 3 | apple | 1 | 19 |
| 4 | orange | 2 | 22 |
| 5 | alin | 4 | 25 |
| 6 | tom | 1 | 18 |
+----+----------+--------+------+mysql> select * from tb2;
+----+------+--------+------+
| id | name | status | age |
+----+------+--------+------+
| 1 | jack | 2 | 18 |
| 5 | ben | 2 | 23 |
| 6 | john | 3 | 19 |
+----+------+--------+------+INNER JOIN
Returns all public records between two tables. If there is no relevant record, it will contain NULL.
mysql> select * from tb1 inner join tb2 on tb1.id = tb2.id;
+----+----------+--------+------+----+------+--------+------+
| id | name | status | age | id | name | status | age |
+----+----------+--------+------+----+------+--------+------+
| 1 | ylspirit | 1 | 22 | 1 | jack | 2 | 18 |
| 5 | alin | 4 | 25 | 5 | ben | 2 | 23 |
| 6 | tom | 1 | 18 | 6 | john | 3 | 19 |
+----+----------+--------+------+----+------+--------+------+LEFT JOIN
LEFT JOIN takes all the intersection data of the two tables plus the remaining data of the LEFT table. If there are no records in the left table in the right table, the row returns NULL.
mysql> select * from tb1 left join tb2 on tb1.id = tb2.id;
+----+----------+--------+------+------+------+--------+------+
| id | name | status | age | id | name | status | age |
+----+----------+--------+------+------+------+--------+------+
| 1 | ylspirit | 1 | 22 | 1 | jack | 2 | 18 |
| 5 | alin | 4 | 25 | 5 | ben | 2 | 23 |
| 6 | tom | 1 | 18 | 6 | john | 3 | 19 |
| 2 | carol | 1 | 21 | NULL | NULL | NULL | NULL |
| 3 | apple | 1 | 19 | NULL | NULL | NULL | NULL |
| 4 | orange | 2 | 22 | NULL | NULL | NULL | NULL |
+----+----------+--------+------+------+------+--------+------+RIGHT JOIN
RIGHT JOIN finds all the intersection data of the two tables plus the remaining data of the RIGHT table. If the record in the right table does not exist in the left table, the row returns NULL.
mysql> select * from tb1 right join tb2 on tb1.id = tb2.id;
+------+----------+--------+------+----+------+--------+------+
| id | name | status | age | id | name | status | age |
+------+----------+--------+------+----+------+--------+------+
| 1 | ylspirit | 1 | 22 | 1 | jack | 2 | 18 |
| 5 | alin | 4 | 25 | 5 | ben | 2 | 23 |
| 6 | tom | 1 | 18 | 6 | john | 3 | 19 |
+------+----------+--------+------+----+------+--------+------+
3 rows in set (0.00 sec)FULL JOIN
FULL JOIN returns all records in both tables. Columns that exist only in one table will show NULL in the opposite table.
mysql>
select * from tb1
left join tb2 on tb1.id = tb2.id
union
select * from tb1
right join tb2 on tb1.id = tb2.id;
+------+----------+--------+------+------+------+--------+------+
| id | name | status | age | id | name | status | age |
+------+----------+--------+------+------+------+--------+------+
| 1 | ylspirit | 1 | 22 | 1 | jack | 2 | 18 |
| 5 | alin | 4 | 25 | 5 | ben | 2 | 23 |
| 6 | tom | 1 | 18 | 6 | john | 3 | 19 |
| 2 | carol | 1 | 21 | NULL | NULL | NULL | NULL |
| 3 | apple | 1 | 19 | NULL | NULL | NULL | NULL |
| 4 | orange | 2 | 22 | NULL | NULL | NULL | NULL |
+------+----------+--------+------+------+------+--------+------+ON and USING syntactic explained
In the MySQL JOIN syntax, the syntax format of ON is:
ON tb1.column = tbl2.columnThe USING syntax can be used to simplify ON syntax when columns in a joined table have the same name:
USING(column_name)So, USING functions like ON, except that USING specifies a property name to join two tables, while ON specifies a condition.
LEFT JOIN
mysql> select * from tb1 left join tb2 using (id);
+----+----------+--------+------+------+--------+------+
| id | name | status | age | name | status | age |
+----+----------+--------+------+------+--------+------+
| 1 | ylspirit | 1 | 22 | jack | 2 | 18 |
| 5 | alin | 4 | 25 | ben | 2 | 23 |
| 6 | tom | 1 | 18 | john | 3 | 19 |
| 2 | carol | 1 | 21 | NULL | NULL | NULL |
| 3 | apple | 1 | 19 | NULL | NULL | NULL |
| 4 | orange | 2 | 22 | NULL | NULL | NULL |
+----+----------+--------+------+------+--------+------+