MySQL tutorial: mysql inner, left, right, full join explained

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.column

The 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 |
+----+----------+--------+------+------+--------+------+