22 SQL Join2

优质

小牛编辑

126浏览

2023-12-01

1. 前言

上一小节中介绍了连接操作中内连接，本小节，我们将学习外连接。

外连接有些许不同，它并不要求两张表中的记录都能够匹配，即使没有匹配到也会保留数据，被保留全部数据的表被称为保留表。

外连接可以根据保留表来进一步分为：左外连接（左边的表数据会被保留），右外连接（右边的表数据会被保留）和全连接（两边的表均被保留）。

外连接没有隐式的连接方式，必须通过 Join 与 On 显式的指定连接方式和连接条件。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_class;
CREATE TABLE imooc_class
(
  id int PRIMARY KEY,
  class_name varchar(20)
);
INSERT INTO imooc_class(id,class_name) VALUES(1,'SQL必知必会'), (2,'C语言入门'),
(3,'JAVA高效编程'),(4,'JVM花落知多少');

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  class_id int references imooc_class(id)
);
INSERT INTO imooc_user(id,username,class_id) VALUES(1,'pedro', 1), (2,'peter', 1),
(3,'faker', 2), (4,'lucy', 4),(5,'jery', NULL);

说明： 我们分别新建了 imooc_class 表——课程表，和 imooc_user 表——用户表；其中 imooc_user 表中的 class_id 作为外键指向 imooc_class 的主键id；若 class_id 为 NULL 则表示该用户暂时还未加入任何课程，否则 class_id 表示用户参加课程的 id 。

2. 左外连接

左外连接（Left Outer Join），简称左连接（Left Join）;若 A 和 B 两表进行左外连接，会在结果中包含左表（即表 A）的所有记录，即使那些记录在右表B 没有符合连接条件相应的匹配记录，未匹配的记录会给予 NULL 填充。

维恩图表示如下：
图片描述

2.1 例1 左连接查询

请书写 SQL 语句，查询imooc_user表中每一个用户姓名和该用户所参加课程的名称。

分析：

由题干可知，查询的结果应是用户姓名以及参加课程，因此 imooc_user 表应该作为保留表；考虑到使用左连接，所以 imooc_user 表是左表，imooc_class 表作为右表。

语句：

整理可得语句如下：

SELECT username,class_name FROM imooc_user LEFT OUTER JOIN imooc_class ON imooc_user.class_id = imooc_class.id;

结果如下：

+----------+---------------+
| username | class_name    |
+----------+---------------+
| pedro    | SQL必知必会    |
| peter    | SQL必知必会    |
| faker    | C语言入门      |
| lucy     | JVM花落知多少  |
| jery     | <null>        |
+----------+---------------+

3. 右外连接

右外连接（Right Outer Join），简称右连接（Right Join）;若 A 和 B 两表进行右外连接，会在结果中包含右表（即表 B）的所有记录，即使那些记录在左表 A 中没有符合连接条件相应的匹配记录，未匹配的记录会给予 NULL 填充。

维恩图表示如下：

图片描述

3.1 例2 右连接

请书写SQL语句，查询imooc_user表中每一门课程和该课程下参与的用户。

分析：

由题干可知，imooc_class 应作为保留表，考虑到使用右连接，因此 imooc_class 是右表；连接条件是外键 class_id。

语句：

整理可得语句如下：

SELECT class_name,username FROM imooc_user RIGHT OUTER JOIN imooc_class ON imooc_class.id = imooc_user.class_id;

结果如下：

+---------------+----------+
| class_name    | username |
+---------------+----------+
| SQL必知必会    | pedro    |
| SQL必知必会    | peter    |
| C语言入门      | faker    |
| JVM花落知多少  | lucy     |
| JAVA高效编程   | <null>   |
+---------------+----------+

提示：SQLite 是不支持右连接的，却可以通过更换保留表的位置用左连接来模拟右连接。

4. 全连接

全连接是左、右外连接的并集。查询结果会包含被连接表的所有记录，若缺少匹配的记录，将以 NULL 填充。

维恩图表示如下：
图片描述

4.1 例3 全连接

请书写 SQL 语句，返回imooc_class和imooc_user表的全连接。

SELECT * FROM imooc_class FULL OUTER JOIN imooc_user ON imooc_class.id = imooc_user.class_id;

查询结果如下：

+--------+----------+----------+--------+---------------+
| id     | username | class_id | id     | class_name    |
+--------+----------+----------+--------+---------------+
| 1      | pedro    | 1        | 1      | SQL必知必会   |
| 2      | peter    | 1        | 1      | SQL必知必会   |
| 3      | faker    | 2        | 2      | C语言入门     |
| 4      | lucy     | 4        | 4      | JVM花落知多少 |
| 5      | jery     | <null>   | <null> | <null>       |
| <null> | <null>   | <null>   | 3      | JAVA高效编程  |
+--------+----------+----------+--------+---------------+

一些数据库，比如 MySQL 是不支持全连接的，但可以通过左、右外连接的并集（Union）来模拟实现，如下：

SELECT *
FROM imooc_user
LEFT JOIN imooc_class 
ON imooc_class.id = imooc_user.class_id
UNION
SELECT *
FROM imooc_user
RIGHT JOIN imooc_class
ON imooc_class.id = imooc_user.class_id
WHERE imooc_user.class_id IS NULL;

提示： SQLite 不支持右连接和全连接，也可以通过左连接来模拟右连接，从而实现全连接。

5. 个人经验

外连接是连接操作的重点，请多多联系并熟练掌握。
左、右连接可以通过更换表的位置来实现同等作用，因此一些数据库只支持左连接。
全连接的使用较少，所以有些数据库会不支持，但是使用左、右连接也可以达到相同效果。