This article mainly introduces the relevant knowledge of JOIN clause in SQL (Structured Query Language), and introduces the common usage of JOIN through usage examples.

Note: the usage example in this article is for MySQL database.

1 Overview
The JOIN clause in SQL is used to combine rows from two or more tables.

In actual database applications, it is often necessary to read data from multiple data tables. At this time, you can use the JOIN clause in SQL statement to query data in two or more data tables.

The usage of JOIN can be divided into the following three categories according to functions:

INNER JOIN (INNER JOIN or equivalent join): obtain records of field matching relationship in two tables;
LEFT JOIN: obtain all records in the left table, even if there is no corresponding matching record in the right table;
RIGHT JOIN: Contrary to LEFT JOIN, it is used to obtain all records in the right table, even if there is no corresponding matching record in the left table.
2 common usage
Two tables are provided (table information is as follows). The usage examples later in this article operate on these two tables.

mysql> select * from roles;
+---------+------------+----------+
| role_id | occupation | camp     |
+---------+------------+----------+
|       1 | warrior    | alliance |
|       2 | paladin    | alliance |
|       3 | rogue      | Horde    |
+---------+------------+----------+
3 rows in set (0.01 sec)
 
mysql> 
mysql> select * from mount_info;
+----------+------------+---------+
| mount_id | mount_name | role_id |
+----------+------------+---------+
|        1 | horse      |       1 |
|        2 | sheep      |       1 |
|        3 | sheep      |       4 |
+----------+------------+---------+
3 rows in set (0.01 sec)
 
mysql> 

2.1 INNER JOIN
The following is an example to introduce the usage of INNER JOIN.

Use INNER JOIN (or omit INNER and use JOIN directly) to connect the above two tables and match the same contents of the field role_id in mount_info and role_id. the relevant commands are as follows:

mysql> SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a INNER JOIN mount_info b ON a.role_id = b.role_id;
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
+---------+------------+----------+------------+
2 rows in set (0.01 sec)
 
mysql> 

The above SQL statements are equivalent to the following statements:

mysql> SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a, mount_info b WHERE a.role_id = b.role_id;
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
+---------+------------+----------+------------+
2 rows in set (0.01 sec)
 
mysql> 

The table association model of INNER JOIN is as follows:

 

 

Note: the green part in the above figure is the internal correlation result of the two tables.

2.2 LEFT JOIN
The following is an example to introduce the usage of LEFT JOIN.

LEFT JOIN is different from INNER JOIN. LEFT JOIN will read all data in the data table on the left, even if there is no corresponding data in the table on the right.

Use LEFT JOIN to connect the above two tables, with roles as the left table and mount_info is the right table, and the related commands are as follows:

mysql> SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a LEFT JOIN mount_info b ON a.role_id = b.role_id;
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
|       2 | paladin    | alliance | NULL       |
|       3 | rogue      | Horde    | NULL       |
+---------+------------+----------+------------+
4 rows in set (0.01 sec)
 
mysql> 

From the above results, we can see that the SQL statement executed above reads all the (SELECT) field data of the left data table roles, even if there is no corresponding role_id field value in the right table mount_info.

The table association model of LEFT JOIN is as follows:

 

 

Note: the green part in the above figure is the left Association result of the two tables.

2.3 RIGHT JOIN
Here is an example to introduce the usage of RIGHT JOIN.

RIGHT JOIN will read all data in the right data table, even if there is no corresponding data in the left table.

Use LEFT JOIN to connect the above two tables, with roles as the left table and mount_info is the right table, and the related commands are as follows:

mysql> SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a RIGHT JOIN mount_info b ON a.role_id = b.role_id;
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
|    NULL | NULL       | NULL     | sheep      |
+---------+------------+----------+------------+
3 rows in set (0.01 sec)
 
mysql> 

From the above results, we can see that the SQL statement executed above reads the data table {mount on the right_ All (SELECT) field data of info ， even if there is no corresponding ， role_id ， field value in the left table ， roles ，.

The table association model of RIGHT JOIN is as follows:

 

Note: the green part in the above figure is the right association result of the two tables.