3. 集合运算

3.1 简介

在数据库中，集合由多条记录组成。具体来说，表、视图和查询的执行结果都是记录的集合，集合中的元素为表、视图或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION、INTERSECT和 EXCEPT 来将检索结果进行并、交和差运算，这类用于进行集合运算的运算符称为集合运算符。下图是常简的几种集合运算：

3.2 表的运算

3.2.1 UNION —— 并运算

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。得到的结果会进行去重。下面来看一个例子，假设我们有一个表product2，里面的元素如下图所示：

下面我们要查询这两个表中所有的商品id和他们的名字，并按照id进行排序，代码如下：

select
    product_id,
    product_name
from
    product
union
select
    product_id,
    product_name
from
    product2
order by product_id;

结果如下：

从结果中可以看出在两个表中都存在的商品编号为“ 0001 ”~“ 0003 ”的 3 条记录只出现了一次。
其实不光是两张表，对同一张表也可以求并集，比如当商店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量，请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。代码如下：

select
    product_id,
    product_name
from
    product
where
    sale_price > 1.5 * purchase_price
union
select
    product_id,
    product_name
from
    product
where
    sale_price < 800;

结果如下：

此部分其实还可以通过一个WHERE语句实现，代码如下：

select
    product_id,
    product_name
from
    product
where
    sale_price > 1.5 * purchase_price or sale_price < 800;

3.2.1.1 UNION ALL

上面也提到了 UNION 语句在产生结果是会进行去重操作，这种去重不仅会去掉两个结果集相互重复的，还会去掉一个结果集中的重复行。假如我们不需要去重，就可以通过在UNION语句后加上ALL来实现这一功能，下面来看一个例子：

select
    product_name
from
    product
union all
select
    product_name
from
    product2;

结果如下：

可以看到得到的结果中包含了重复项。

3.2.1.2 隐式数据类型转换

通常来说，我们会把类型完全一致，并且代表相同属性的列使用 UNION 合并到一起显示，但有时即使数据类型不完全相同，也会通过隐式类型转换来将两个类型不同的列放在一列里显示，例如字符串和数值类型:
select
product_name, product_type, ‘1’
from
product
union all
select
product_name, product_type, sale_price
from
product2;
结果如下：

【注意】：

• Hive 中进行 join 关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

3.2.2 INTERSECT —— 交运算

对两个集合求交其实就是求两个集合的公共的元素。截至 MySQL 8.0，其仍然不支持交运算符INTERSECT，交运算需要借助连接操作实现，具体后面会介绍。

3.2.3 差集和补集运算

3.2.3.1 EXCEPT——差（补）集运算

差集运算相当于求在一个表中但是不在另一个表中的元素。但是 MySQL 8.0 还不支持 EXCEPT 运算符。不过可以借助 NOT IN 谓词实现这一过程，比如我们要找在product中但是不在product2中的物品，代码如下：
select
product_name,
product_type
from
product
where product_name not in (
select
product_name
from
product2
);
结果如下：

image-20220622201739626
假如B中元素A中都存在，但是A中有的元素B中不一定有的话，该操作就是求补集。

3.2.3.2 对称差集运算

对两个集合求对称差集其实就是求仅属于集合A或仅属于集合B的元素构成的集合。最简单的办法就是并集减交集，但是MySQL 8.0 里，由于两个表或查询结果的并不能直接求出来，因此并不适合使用上述思路来求对称差。好在还有差集运算可以使用，从直观上就能看出来，两个集合的对称差等 于A-B并上B-A，因此实践中可以用这个思路来求对称差。代码如下：
select
product_name,
product_type
from
product
where product_name not in (
select
product_name
from
product2
)
union
select
product_name,
product_type
from
product2
where product_name not in (
select
product_name
from
product
);
结果如下：

image-20220622202330132

3.3 连接操作（JOIN）

3.3.1 简介

上一节我们介绍了 UNION 运算符等实现的集合运算，但是这些集合运算的特征就是以行方向为单位进行操作。即这些操作会导致记录行数的增减。但是这些操作却不能改变列的变化，最多是求出当前表的隐身列，当我们想要从多个表获取信息时，比如想要找出某个商店里的衣服类商品的名称、数量及价格等信息，则必须分别从 shopProduct 表和 product 表获取信息。如下图所示：

image-20220622212302092
这就需要本节介绍的连接操作，连接操作就是使用某种关联条件（一般是使用相等判断谓词”=”），将其他表中的列添加过来，进行“添加列”的集合运算。可以说连接是 SQL 查询的核心操作，掌握了连结，能够从两张甚至多张表中获取列，能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式，以及进行一些更加复杂的查询。

3.3.2 内连接

内连接的语法格式如下：
FROM INNER JOIN ON
其中 INNER 关键字表示使用了内连接，至于内连接的含义可以从下面的使用中慢慢体会。

3.3.2.1 使用内连接从两个表中获取信息

回看简介中的例子，先来分别观察所涉及的表，Product 表保存了商品编号,商品名称,商品种类等信息，这个表可以提供关于衣服种类的衣服的详细信息，但是不能提供商店信息：

image-20220622212407318
我们接下来观察 ShopProduct 表，这个表里有商店编号名称，商店的商品编号及数量。但要想获取商品的种类及名称售价等信息，则必须借助于 Product 表：

image-20220622212448660
所以问题的关键是我们需要找到一个类似于“轴”或“桥梁”的公共列，然后基于这个公共列连接两张表。通过观察可以得出，上面两张表的公共列是商品编号，因此可以用这个商品编号列来作为连接的“桥梁”连接两个表。具体可以从下图中看出：

image-20220622212724097
然后回归最开始的例子，我们可以通过直接对两个表进行内连接，就可以查到所需的列，具体代码如下：
select
sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_name,
p.product_type,
p.sale_price,
sp.quantity
from
shop_product as sp inner join product p on sp.product_id = p.product_id;
结果如下：

image-20220622213217516
使用内连接需要注意以下几点：

• 需要在 FROM 子句中使用多张表；
• 必须使用 ON 子句来指定连结条件；
• SELECT 子句中的列最好按照 表名.列名 的格式来使用

3.3.2.2 结合 WHERE 子句使用

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选，则需要把 WHERE 子句写在 ON 子句的后边。比如我们需要查询商店名称是东京且商品类别为衣服的商品的各种特征的语句如下：
select
sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_name,
p.product_type,
p.sale_price,
sp.quantity
from
shop_product as sp inner join product p on sp.product_id = p.product_id
where
sp.shop_name = ‘东京’ and p.product_type = ‘衣服’;
结果如下：

该语句的执行过程为：FROM子句 -> WHERE 子句 -> SELECT子句。也就是说，是两张表先进行连接操作，然后再根据WHERE子句筛选需要的列。此外，还有一种方法就是先分别在两张表里做筛选，然后把筛选条件按表分拆，再将结果表进行连接。比如下面的代码：
select
sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_name,
p.product_type,
p.sale_price,
sp.quantity
from (select
shop_id,
shop_name,
quantity,
product_id
from
shop_product
where
shop_name = ‘东京’) as sp
inner join (
select
product_id,
product_name,
product_type,
sale_price
from
product
where
product_type = ‘衣服’
) p on sp.product_id = p.product_id;

3.3.2.3 结合GROUP BY子句使用内连接

结合GROUP BY子句使用内连接需要根据分组列位于哪个表区别对待，最简单的情况就是在内连接前使用GROUPBY子句。但是如果分组列和被聚合的列不在同一张表，且二者都未被用于连接两张表，则只能先连接后聚合，比如求每个商店中，售价最高的商品的售价的代码如下：
select
sp.shop_id,
sp.shop_name,
max(p.sale_price) as max_price
from
shop_product sp inner join product p on sp.product_id = p.product_id
group by sp.shop_id, sp.shop_name;
结果如下图所示：

3.3.2.4 自连接

前面介绍的内连接操作都是对于两张表，但是实际上一张表也能做内连接，这种内连接称为自连接。需要注意的是，自连接并不是区分于内连结和外连结的第三种连结，它可以是外连接也可以是内连接。

3.3.2.5 内连接和关联子查询

会议上一章关联子查询中的一个例子：找出每个商品种类当中售价高于该类商品的平均售价的商品。当时的解决方法如下（使用关联子查询）：
select
product_type,
product_name,
sale_price
from
product as p1
where
sale_price > (
select
avg(sale_price)
from
product as p2
where
p1.product_type = p2.product_type
group by
product_type
);
本节我们通过内连接解决这一问题：

• 首先用GROUPBY语句按商品类别分类计算每类商品的平均价格
• 然后将这个表与原表按照商品类别进行内连接
• 最后增加WHERE语句找出价格高于平均价格的商品

完整的代码如下：
select
p1.product_type,
p1.product_name,
p1.sale_price
from
product p1 inner join (
select
product_type,
avg(sale_price) as avg_price
from
product
group by
product_type
) p2 on p1.product_type = p2.product_type
where
sale_price > avg_price;
结果如下：

image-20220623082659853

3.3.2.6 自然连接

自然连接并不是区别于内连接和外连接的第三种连接，它其实是内连接的一种特例——当两个表进行自然连结时，会按照两个表中都包含的列名来进行等值内连结，此时无需使用 ON 来指定连接条件。比如执行以下代码：
select from product natural join shop_product;
结果如下：

image-20220623082945849
从结果可以看出，该语句对应的内连接语句为：
select from product p inner join shop_product sp on p.product_id = sp.product_id;
但是自然连接是不能处理空值的，因为自然连接实际上是在逐字段进行等值连结，而控制时不等于空值的，比如求表 Product 和表 Product2 中的公共部分的自然连接代码：
select from product natural join product2;
结果如下，可以看到少了运动 T 恤这一行数据，这是因为运动 T 恤的 regist_date 字段为空：

image-20220623083648195
可以通过以下方式进行优化，即找出所需的字段，再进行自然连接，代码如下：
select

from (
select
product_id,
product_name
from
product
) as p1
natural join (
select
product_id,
product_name
from
product2
) as p2;
结果如下：

3.3.2.7 使用连接运算求交集

由于 MySQL 8.0 里没有交集运算，但是可以用连结来实现求交集的运算。比如求 Product 表和 Product2 表的交集的代码如下：
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON (P1.product_id = P2.product_id
AND P1.product_name = P2.product_name
AND P1.product_type = P2.product_type
AND P1.sale_price = P2.sale_price
AND P1.regist_date = P2.regist_date);
结果如下：

这里少的数据同样是因为缺少 regist_date 字段，可以通过以下代码进行优化：
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON P1.product_id = P2.product_id;

3.3.3 外连接

内连接会丢弃两张表中不满足 ON 条件的行，如果我们需要保留全部或者部分不满足条件的列就需要外连接。外连接会根据不同的种类有选择地保留无法匹配到的行。按照保留的行位于哪张表，外连接可以分为以下三种：

• 左外连接：保存左表不满足条件的行，此时对应右表的行均为缺失值；
• 右外连接：保存右表不满足条件的行，此时对应左表的行均为缺失值；
• 全外连接：保存全部不满足条件的行。

三种外连接的语法如下：
— 左连接
FROM LEFT OUTER JOIN ON
— 右连接
FROM RIGHT OUTER JOIN ON
— 全外连接
FROM FULL OUTER JOIN ON
由于连接时左表和右表的位置可以交换，所以后面的例子我们主要使用左连接介绍

3.3.3.1 使用左外连接从两张表中获得信息

仔细观察将 shopproduct 和 product 进行内连结前后的结果的话，可以发现 product 表中有两种商品并未在内连结的结果里，就是说这两种商品并未在任何商店有售（这通常意味着比较重要的业务信息，例如这两种商品在所有商店都处于缺货状态，需要及时补货）。现在我们把之前内连结的 SELECT 语句转换为左连结来看看结果：
select
sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_name,
p.sale_price
from
product p left outer join shop_product sp on sp.product_id = p.product_id;
结果如下：

image-20220623085559411
可以发现有两种商品：高压锅和圆珠笔，在所有商店都没有销售。
使用外连接有以下要点：

• 选取出单张表中全部的信息，即将需要取出所有信息的表作为主表；
• 使用 LEFT、RIGHT 来指定主表。

3.3.3.2 结合 WHERE 子句使用左外连接

在结合WHERE子句使用外连接时，产生的结果可能和内连接的结果不同，会包含那些主表中无法匹配到的行，并用缺失值填写另一表中的列，由于这些行的存在，因此在外连结时使用WHERE子句时结果可能不太一样。下面来看一个例子，假如我们需要从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店，代码如下：
select
p.product_id,
p.product_name,
p.sale_price,
sp.shop_id,
sp.shop_name,
sp.quantity
from
product p left outer join shop_product sp on p.product_id = sp.product_id
where
quantity < 50;
结果如下：

但是结果中没有高压锅和圆珠笔两种商品，而这两种在所有商店都无货, 所以也应该包括在内，很显然还是因为没有选择数量为空的情况。语句如下：
select
p.product_id,
p.product_name,
p.sale_price,
sp.shop_id,
sp.shop_name,
sp.quantity
from
product p left outer join shop_product sp on p.product_id = sp.product_id
where
quantity < 50 or quantity is null;
结果如下：

3.3.3.3 全外连接

注意 MySQL 8.0 目前还不支持全外连结，不过可以对左连接和右连接的结果进行 UNION 来实现全外连结。

3.3.4 多表连接

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

3.3.4.1 多表进行内连接

根据 nventoryProduct 表、ShopProduct 表和 Product 表，使用内连接找出每个商店都有那些商品，每种商品的库存总量分别是多少。代码如下：
select
sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_name,
p.sale_price,
ip.inventory_quantity
from
shop_product sp inner join product p on sp.product_id = p.product_id inner join inventoryproduct ip on sp.product_id = ip.product_id
where
ip.inventory_id = ‘P001’;
结果如下：

可以看到连接第三张表时，也是通过 ON 子句指定条件。如果想要把连接的表增加到3张以上，同样也是相同的方法。

3.3.4.2 多表进行外连接

正如之前所学的，外连接一般能比内连接有更多的行，从而能够比内连接给出更多关于主表的信息，多表连接的时候使用外连接也有同样的作用。比如：
select
sp.product_id,
p.product_name,
p.sale_price,
sp.shop_id,
sp.shop_name,
ip.inventory_quantity
from
product p left outer join shop_product sp on sp.product_id = p.product_id left outer join inventoryproduct ip on p.product_id = ip.product_id
where
ip.inventory_id = ‘P001’;
结果如下：

image-20220623092252102

3.3.5 非等值连接

除了等值连接外，也可以使用比较运算符来进行连接。实际上，括比较运算符(<, <=, >, >=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连接条件。下面来看一个例子。假如我们需要对 Product 表中的商品按照售价赋予排名。一个从集合论触发，使用左连接的思路是，对每一种商品，找出售价不低于它的所有商品，并进行计数。实现代码如下：
select
product_id,
product_name,
sale_price,
count(p2_id) as my_rank
from (
select
p1.product_id,
p1.product_name,
p1.sale_price,
p2.product_id as p2_id,
p2.product_id as p2_name,
p2.sale_price as p2_price
from
product p1 left outer join product p2
on
p1.sale_price <= p2.sale_price
) as x
group by product_id, product_name, sale_price
order by my_rank;
结果如下：

3.3.6 交叉连接 —— CROSS JOIN

之前的无论是外连接内连接，一个共同的必备条件就是连接条件 —— ON 子句，用于指定连接的条件。如果不使用这个条件的话会发现结果有很多行。而去掉去掉 ON 子句，就是所谓的交叉连接，也称为笛卡尔积。后者是一个数学术语。两个集合做笛卡尔积，就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。交叉连接的语法有以下几种形式：
— 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM ShopProduct AS SP
CROSS JOIN Product AS P;
— 2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM ShopProduct AS SP , Product AS P;
可以试着执行以下 以上的语句，会发现结果非常的大，这是因为笛卡尔积产生的结果是两表中的所有行数的乘积，因此实际查询时要尽量避免使用笛卡尔积。

3.4 练习题

• 找出 product 和 product2 中售价高于 500 的商品的基本信息
• select
  from product
  where sale_price > 500
  union
  select
  from product2
  where sale_price > 500;
• 结果如下：
• 

• 借助对称差的实现方式，求 product 和 product2 的交集
• select
  from (
  select
  
  from
  product
  union
  select
  *
  from
  product2
  ) p
  where p.product_name not in (
  select product_name
  from product
  where product_name not in (
  select product_name
  from product2
  )
  union
  select product_name
  from product2
  where product_name not in (
  select product_name
  from product
  ));
• 结果如下：
• 

• 求出每类商品中售价最高的商品都在哪些商店有售
• select
  p.product_id,
  p.product_name,
  p.product_type,
  sp.shop_name
  from product p inner join shop_product sp on p.product_id = sp.product_id
  where sale_price in (
  select
  max(sale_price)
  from
  product p_sub
  where
  p.product_type = p_sub.product_type
  group by
  product_type
  );
• 结果如下：

• 

• 分别使用内连结和关联子查询每一类商品中售价最高的商品

• — 1.关联子查询
  select
  p.product_id,
  p.product_name,
  p.product_type,
  p.sale_price
  from product p
  where sale_price in (
  select
  max(sale_price)
  from
  product p_sub
  where
  p.product_type = p_sub.product_type
  group by
  product_type
  );
  
  — 2. 内连接
  select
  p1.product_id,
  p1.product_name,
  p1.product_type,
  p1.sale_price
  from
  product p1 inner join (
  select
  product_type,
  max(sale_price) as max_price
  from
  product
  group by
  product_type
  ) p2 on p1.product_type = p2.product_type
  where
  sale_price = max_price;
• 结果如下：
• 

• 用关联子查询实现：在 product 表中，取出 product_id，product_name 和 sale_price，并按照商品的售价从低到高进行排序、对售价进行累计求和
• （待补充）

参考资料

• Wonderful-SQL：https://github.com/datawhalechina/wonderful-sql