SQL Join2
1. 前言
上一小节中介绍了连接操作中内连接,本小节,我们将学习外连接
。
外连接有些许不同,它并不要求两张表中的记录都能够匹配,即使没有匹配到也会保留数据,被保留全部数据的表被称为保留表。
外连接可以根据保留表来进一步分为:左外连接
(左边的表数据会被保留),右外连接
(右边的表数据会被保留)和(两边的表均被保留)。
外连接没有隐式的连接方式,必须通过 Join 与 On 显式的指定连接方式和连接条件。
本小节测试数据如下,请先在中执行:
DROP TABLE IF EXISTS imooc_class;CREATE TABLE imooc_class( id int PRIMARY KEY, class_name varchar());INSERT INTO imooc_class(id,class_name) VALUES(,'必知必会'), (,'C语言入门'),(,'JAVA高效编程'),(,'JVM花落知多少');DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), class_id int references imooc_class(id));INSERT INTO imooc_user(id,username,class_id) VALUES(,'pedro', ), (,'peter', ),(,'faker', ), (,'lucy', ),(,'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 填充。
维恩图表示如下:
请书写 语句,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 | 必知必会 | | peter | 必知必会 | | faker | C语言入门 | | lucy | JVM花落知多少 | | jery | <null> | +----------+---------------+
3. 右外连接
右外连接(Right Outer Join),简称右连接(Right Join);若 A 和 B 两表进行右外连接,会在结果中包含右表(即表 B)的所有记录,即使那些记录在左表 A 中没有符合连接条件相应的匹配记录,未匹配的记录会给予 NULL 填充。
维恩图表示如下:
请书写语句,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 | +---------------+----------+ | 必知必会 | pedro | | 必知必会 | peter | | C语言入门 | faker | | JVM花落知多少 | lucy | | JAVA高效编程 | <null> | +---------------+----------+
:ite 是右连接的,却可以通过更换保留表的位置用左连接来模拟右连接。
4.
是左、右外连接的并集。结果会包含被连接表的所有记录,若缺少匹配的记录,将以 NULL 填充。
维恩图表示如下:
请书写 语句,返回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 | 必知必会 | | 2 | peter | 1 | 1 | 必知必会 | | 3 | faker | 2 | 2 | C语言入门 | | 4 | lucy | 4 | 4 | JVM花落知多少 | | 5 | jery | <null> | <null> | <null> | | <null> | <null> | <null> | 3 | JAVA高效编程 | +--------+----------+----------+--------+---------------+
一些,比如 是的,但可以通过左、右外连接的并集(Union)来模拟实现,如下:
SELECT *FROM imooc_userLEFT JOIN imooc_class ON imooc_class.id = imooc_user.class_idUNIONSELECT *FROM imooc_userRIGHT JOIN imooc_classON imooc_class.id = imooc_user.class_idWHERE imooc_user.class_id IS NULL;
: ite 右连接和,也可以通过左连接来模拟右连接,从而实现。
5. 个人经验
外连接是连接操作的重点
,请多多联系并熟练掌握。
左、右连接可以通过更换表的位置来实现同等作用,因此一些只左连接。
的使用较少,所以有些会,但是使用左、右连接也可以达到相同。