数据库 / 22 SQL Join2

SQL Join2

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_classimooc_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. 个人经验

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