阿里云天池龙珠计划SQL训练营Task04：集合运算-表的加减法和join等

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

4.1表的加减法

4.1.1 什么是集合运算

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

以下的文氏图展示了几种集合的基本运算.

4.1.2 表的加法–UNION

4.1.2.1 UNION

建表代码及数据导入请使用第一章提供的代码.

接下来我们演示UNION的具体用法及查询结果:

SELECT product_id, product_name
FROM product
UNION
SELECT product_id, product_name
FROM product2;
上述结果包含了两张表中的全部商品. 你会发现,这就是我们在学校学过的集合中的并集运算,通过文氏图会看得更清晰（图 7-1):

通过观察可以发现,商品编号为“ 0001 ”~“ 0003 ”的 3 条记录在两个表中都存在,因此大家可能会认为结果中会出现重复的记录,但是 UNION 等集合运算符通常都会除去重复的记录.
上述查询是对不同的两张表进行求并集运算. 对于同一张表, 实际上也是可以进行求并集的.

4.1.2.2UNION 与 OR 谓词

对于上边的练习题, 如果你已经正确地写出来查询, 你会发现, 使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果.

那么是不是就没必要引入 UNION 了呢? 当然不是这样的. 确实, 对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.

而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION.

4.1.2.3 包含重复行的集合运算 UNION ALL

在1.1.1 中我们发现, SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了.

例如, 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量, 第一步,就需要将两个表的商品种类字段选出来, 然后使用 UNION ALL 进行不去重地合并. 接下来再对两个表的结果按 product_type 字段分组计数.

– 保留重复行
SELECT product_id, product_name
FROM product
UNION ALL
SELECT product_id, product_name
FROM product2;
查询结果如下:

4.1.2.5隐式类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, ‘1’
FROM product
UNION
SELECT product_id, product_name,sale_price
FROM product2;
上述查询能够正确执行,得到如下结果:

4.1.3 MySQL 8.0 不支持交运算INTERSECT

集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过文氏图就可以很直观地看到它的意义.

虽然集合的交运算在SQL标准中已经出现多年了, 然而很遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作.

SELECT product_id, product_name
FROM product

INTERSECT
SELECT product_id, product_name
FROM product2
错误代码：1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT product_id, product_name
FROM product2

4.1.4 差集,补集与表的减法

求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算

MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法.
– 使用 IN 子句的实现方法
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM product2)

4.1.4.2 EXCEPT 与 NOT 谓词

通过上述练习题的MySQL解法, 我们发现, 使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果.

4.2连结(JOIN)

4.2.1 内连结(INNER JOIN)

内连结的语法格式是:

– 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
其中 INNER 关键词表示使用了内连结, 至于内连结的涵义, 目前暂时可以不必细究.
关于内连结,需要注意以下三点:

• 要点一: 进行连结时需要在 FROM 子句中使用多张表.
• 要点二:必须使用 ON 子句来指定连结条件.
  在进行内连结时 ON 子句是必不可少的(大家可以试试去掉上述查询的 ON 子句后会有什么结果).ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用。
• 要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.

4.2.1.2 结合 WHERE 子句使用内连结

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.
执行顺序:
FROM 子句->WHERE 子句->SELECT 子句
也就是说, 两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列.

4.2.1.3结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.

最简单的情形, 是在内连结之前就使用 GROUP BY 子句.

但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合.

4.2.1.4自连结(SELF JOIN)

之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法.

4.2.1.5内连结与关联子查询

4.2.1.6自然连结(NATURAL JOIN)

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.

4.2.1.7使用连结求交集

4.2.2 外连结(OUTER JOIN)

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行.

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

三种外连结的对应语法分别为:

– 左连结
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
– 右连结
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
– 全外连结
FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

4.2.2.1 左连结与右连接

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.接下来我们先以左连结为例进行学习. 所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果. 稍后再介绍全外连结的概念.

4.2.2.2 使用左连结从两个表获取信息

如果你仔细观察过将shopproduct 和product 进行内连结前后的结果的话, 你就会发现,product 表中有两种商品并未在内连结的结果里, 就是说, 这两种商品并未在任何商店有售(这通常意味着比较重要的业务信息, 例如, 这两种商品在所有商店都处于缺货状态, 需要及时补货). 现在, 让我们先把之前内连结的 SELECT 语句转换为左连结试试看吧.

• 外连结要点 1: 选取出单张表中全部的信息
• 外连结要点 2:使用 LEFT、RIGHT 来指定主表.

4.2.2.3结合 WHERE 子句使用左连结

可以把WHERE子句挪到外连结之前进行: 先写个子查询,用来从副表中条件后， 再把这个子查询和主表连结起来，避免最终结果过滤掉一些记录。

4.2.2.4在 MySQL 中实现全外连结

有了对左连结和右连结的了解, 就不难理解全外连结的含义了. 全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。

遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

4.2.3多表连结

通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。