阿里天池AI训练营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

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

上述结果包含了两张表中的全部商品. 你会发现,这就是我们在学校学过的集合中的并集运算,通过文氏图会看得更清晰

通过观察可以发现,商品编号为“ 0001 ”~“ 0003 ”的 3 条记录在两个表中都存在,因此大家可能会认为结果中会出现重复的记录,但是 UNION 等集合运算符通常都会除去重复的记录.

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

对两个结果集进行并集操作，包括重复行，不会对结果进行排序。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

4.1.2.4[扩展阅读]bag 模型与 set 模型

在高中数学课上我们就学过, 集合的一个显著的特征就是集合中的元素都是互异的. 当我们把数据库中的表看作是集合的时候, 实际上存在一些问题的: 不论是有意的设计或无意的过失, 很多数据库中的表包含了重复的行.

Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素.

通过上述 bag 与 set 定义之间的差别我们就发现, 使用 bag 模型来描述数据库中的表在很多时候更加合适.

是否允许元素重复导致了 set 和 bag 的并交差等运算都存在一些区别. 以 bag 的交为例, 由于 bag 允许元素重复出现, 对于两个 bag, 他们的并运算会按照: 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数 这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}.

4.1.2.5隐式类型转换

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

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容.

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 
 UNION
 
SELECT 'chars', 123,  null

上述代码的查询结果:

4.2连结(JOIN)

表连接有3种

• 内连接 inner join
• 左连接 left join
• 右连接 right join

4.2.1 内连结(INNER JOIN)

-- 内连结 方法一
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
-- 内连结 方法二
FROM <tb_1>,<tb_2>
WHERE <tb_1>.列名 = <tb_2>.列名
-- 内连结 方法三 自然连接
FROM  <tb_1> NATURAL JOIN <tb_2>

取两个表连接部分的交集

4.2.2 左连结与右连接

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>

4.2.2.1在 MySQL 中实现全外连结

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

4.2.3ON 子句进阶–非等值连结

比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.