本文主要介绍SQL(Structured Query Language)中连接(JOIN)子句的相关知识,同时通过用法示例介绍连接子句的常见用法。
说明:本文的用法示例是面向MySQL数据库的。
1 概述
SQL中的JOIN子句用于把来自两个或多个表的行结合起来。
在实际的数据库应用中,经常需要从多个数据表中读取数据,这时就可以使用SQL语句中的连接(JOIN)子句,在两个或多个数据表中查询数据。
JOIN的用法可按功能划分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录;
- LEFT JOIN(左连接):获取左表中的所有记录,即使在右表没有对应匹配的记录;
- RIGHT JOIN(右连接):与LEFT JOIN相反,用于获取右表中的所有记录,即使左表没有对应匹配的记录。
2 常见用法
现提供如下两个数据库表(表信息如下),本文后面的用法示例,均是对这两个表进行操作的。
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
下面通过一个示例,介绍INNER JOIN的用法。
使用INNER JOIN(也可以省略INNER直接使用JOIN)来连接上面两张表,匹配mount_info和roles中字段role_id相同的内容,相关命令如下:
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>
上述SQL语句与下列语句等价:
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>
INNER JOIN的表关联模型如下:
说明:上图中的绿色部分,为两表的内关联结果。
2.2 LEFT JOIN
下面通过一个示例,介绍LEFT JOIN的用法。
LEFT JOIN与INNER JOIN有所不同,LEFT JOIN会读取左侧数据表的全部数据,即使右侧表中无对应数据。
使用LEFT JOIN来连接上面两张表,以roles为左侧表、mount_info为右侧表,相关命令如下:
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>
通过上述结果可知,上面执行的SQL语句读取左边的数据表roles的所有的(SELECT)字段数据,即使在右侧表mount_info中没有对应的role_id字段值。
LEFT JOIN的表关联模型如下:
说明:上图中的绿色部分,为两表的左关联结果。
2.3 RIGHT JOIN
下面通过一个示例,介绍RIGHT JOIN的用法。
RIGHT JOIN会读取右侧数据表的全部数据,即便左侧表无对应数据。
使用RIGHT JOIN来连接上面两张表,以roles为左侧表、mount_info为右侧表,相关命令如下:
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>
通过上述结果可知,上面执行的SQL语句读取右侧的数据表mount_info的所有的(SELECT)字段数据,即使在左侧表roles中没有对应的role_id字段值。
RIGHT JOIN的表关联模型如下:
说明:上图中的绿色部分,为两表的右关联结果。