task04 第四章 集合运算

目录

课程链接

4.1 表的加减法

4.1.1 表的加法（UNION）

4.1.2 交集（INNER JOIN）

4.1.3 表的减法（NOT IN）

4.2 连结 (JOIN)

4.2.1 内连结 (INNER JOIN)

4.2.1.1 结合 WHERE 子句

4.2.1.2 结合 GROUP BY 子句

4.2.1.3 自连结

4.2.1.4 内连结与关联子查询

4.2.1.5 自然连结 (NATURAL JOIN)

4.2.2 外连结 (OUTER JOIN)

 4.2.2.1 结合 WHERE 子句

4.2.2.2 全外连结

4.2.3 多表连结

4.2.3.1 多表进行内连结

4.2.3.2 多表进行外连结

4.2.4 ON 子句进阶——非等值连结

4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)

4.2.6 连结的特定语法和过时语法

本章练习题

4.1

4.2

4.3

4.4

4.5

---

课程链接

科鲸信息技术

4.1 表的加减法

4.1.1 表的加法（UNION）

• union 可以对两个查询结果取并集，并自动去除重复行。想要保留重复行可使用 union all
• union 和 or 谓词 可以相互替换，如找出成本利润率不足 30%或成本利润率未知的商品。

-- 使用or谓词
SELECT *
FROM product
WHERE sale_price < 1.3 * purchase_price
OR 1.3 * purchase_price IS NULL;

-- 使用union
SELECT *
FROM product
WHERE sale_price < 1.3 * purchase_price
UNION 
SELECT *
FROM product
WHERE 1.3 * purchase_price IS NULL;

【隐式数据类型转换】通常情况下会使用 union 把类型相同的列合并在一起显示，但有时数据类型不一致也可以同列。如字符串和数值类型可在同列，时间日期类型与字符串、数值和null值均可兼容在同列显示。

（可参考：Data type conversion (Database Engine) - SQL Server | Microsoft Learn）

4.1.2 交集（INNER JOIN）

MySQL 8.0 不支持交运算 intersect，需要用 inner join 来求得交集：

SELECT p1.product_id, p1.product_name
  FROM product p1
INNER JOIN product2 p2
ON p1.product_id=p2.product_id;

4.1.3 表的减法（NOT IN）

• MySQL 8.0 还不支持减法运算符 except ，可以用 not in 谓词来实现表的减法：

SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2);

• 对称差：两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。也可以用 not in 来实现

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM product2
 WHERE product_id NOT IN (SELECT product_id FROM Product);

4.2 连结 (JOIN)

union 和 intersect 等集合运算是以行方向为单位进行操作，即会导致记录行数的增减，其中 union 会增加记录行数，intersect（只返回两个集合中都存在的行，若集合相同，行数不会减少）， except （基于第一个集合减去第二个集合中出现的行，若第二个集合中没有第一个集合中的行，行数不会减少）。

连结（JOIN）就是使用某种关联条件（一般是使用相等判断谓词"="），将其他表中的列添加过来，进行“添加列”的集合运算。可从两张及以上表中获取列，提供了一种灵活而强大的数据检索方式。

4.2.1 内连结 (INNER JOIN)

关键点是找到一个桥梁，即两张表的公共列作为连接条件：

如 找出东京商店里的衣服类商品的商品名称、商品价格、商品种类、商品数量信息。商品表中无商店信息，商店表里无商品信息，故需将两表连结起来，通过二者的公共列 product_id。

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id;

【注】

① 进行连结时需要在 from 子句中使用多张表。

FROM ShopProduct AS SP 
INNER JOIN Product AS P

② 必须使用 on 子句来指定连结条件（基本上, 它能起到与 WHERE 相同的筛选作用）。

③ select 子句中的列最好使用 “表名.列名” 的格式来使用。

一是易于阅读，二是避免报错。

4.2.1.1 结合 WHERE 子句

现在已经连结成功，可继续使用 WHERE 对检索结果进行筛选。

• 法一：将上述查询作为子查询封装，在外层查询增加筛选条件

SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROM ShopProduct AS SP
         INNER JOIN Product AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;

• 法二【标准写法】：由于查询的执行顺序是 FROM → WHERE → SELECT，内连结包含在 FROM 步骤内，故直接在内连结后面写 WHERE 即可

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

•  法三【不建议用】：不常见的做法，将 WHERE 筛选条件放在 ON 内，最好用括号括住

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON (SP.product_id = P.product_id
   AND SP.shop_name = '东京'
   AND P.product_type = '衣服') ;

• 法四：采用任务分解的方法，先在两张表分布 WHERE 筛选后，再将这两个子查询连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
        SELECT *
          FROM ShopProduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
   (SELECT *
      FROM Product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

练习题:

分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

代码：

-- 不使用子查询
SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	sp.quantity,
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	shopproduct sp
	INNER JOIN product p ON sp.product_id = p.product_id 
WHERE
	sp.shop_name = '东京' 
	AND p.sale_price < 2000;
	
-- 使用子查询
SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	sp.quantity,
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	( SELECT * FROM shopproduct WHERE shop_name = '东京' ) AS sp
	INNER JOIN ( SELECT * FROM product WHERE sale_price < 2000 ) AS p ON sp.product_id = p.product_id;

4.2.1.2 结合 GROUP BY 子句

先连接后聚合，还是先聚合后连接？

① 如果分组列和被聚合的列在同一张表中：

        最简单的做法是，在执行连接之前，先在单个表上完成GROUP BY和聚合操作。这样可以减少连接操作的数据量，提高效率。

② 分组列和被聚合的列分布在不同的表中，且二者之一或两者都是连接条件的一部分：

        在这种情况下，通常需要首先执行连接操作，因为分组或聚合的信息依赖于连接结果。连接之后，再根据需求应用GROUP BY和相应的聚合函数。

③分组列和被聚合的列都不参与连接条件：

        即使这两个列位于不同的表中，理论上你仍然可以在连接之前尝试对单个表进行聚合，但这通常只在你能确保这样做不影响结果正确性的前提下才可行。实践中，大多数情况会先执行连接，确保所有相关数据被正确整合后再进行分组和聚合。

练习题:

每个商店中, 售价最高的商品的售价分别是多少?

代码：

SELECT
	sp.shop_id,
	sp.shop_name,
	MAX( p.sale_price ) AS max_sale_price 
FROM
	shopproduct sp
	INNER JOIN product p ON sp.product_id = p.product_id 
GROUP BY
	sp.shop_id,
	sp.shop_name;

结果：

思考题:

上述查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?

代码：

SELECT
	sp.shop_id,
	sp.shop_name,
	p.product_name,
	p.sale_price AS max_sale_price 
FROM
	(
	SELECT
		sp.shop_id,
		MAX( p.sale_price ) AS max_sale_price 
	FROM
		shopproduct sp
		INNER JOIN product p ON sp.product_id = p.product_id 
	GROUP BY
		sp.shop_id 
	) AS subquery
	INNER JOIN shopproduct AS sp ON sp.shop_id = subquery.shop_id
	INNER JOIN product AS p ON p.product_id = sp.product_id 
	AND p.sale_price = subquery.max_sale_price;

结果：

4.2.1.3 自连结

一张表与自身作连结，这种连接称之为自连结（SELF JOIN）。需要注意，自连结并不是区分于内连结和外连结的第三种连结，自连结可以是外连结也可以是内连结，它是不同于内连结外连结的另一个连结的分类方法。

4.2.1.4 内连结与关联子查询

找出每个商品种类当中售价高于该类商品的平均售价的商品。

代码：

-- 子查询方式
SELECT
	product_type,
	product_name,
	sale_price 
FROM
	product AS p1 
WHERE
	sale_price > ( SELECT AVG( sale_price ) FROM productins AS p2 WHERE p1.product_type = p2.product_type GROUP BY product_type );

-- 内连结方式
SELECT
	p1.product_type,
	p1.product_name,
	p1.sale_price,
	p2.avg_price 
FROM
	product AS p1
	INNER JOIN ( SELECT product_type, AVG( sale_price ) AS avg_price FROM product GROUP BY product_type ) AS p2 ON p1.product_type = p2.product_type 
WHERE
	p1.sale_price > p2.avg_price;

结果：

4.2.1.5 自然连结 (NATURAL JOIN)

在数据库操作中，自然连接（natural join）是基于两个表中共有且名称相同的列自动执行等值匹配的一种连接方式（无需使用 ON 来指定连接条件，属于内连结的一种特例）。这意味着，仅当两个表中相同名称的列取值相等时，相应的行才会在结果集中组合出现。特别地，如果这些共有的列中存在 null 值，那么包含 null 值的行将不会与另一表中的任何行匹配，因而不会出现在最终的查询结果中，因为 null 值在等值比较时既不视为真也不视为假。

因此，若要确保连接操作不遗漏因 null 值而未匹配的行，需谨慎考虑是否使用自然连接，或者改用如内连接（INNER JOIN）、左连接（LEFT JOIN）等显式连接类型，并明确指定连接条件，尤其是当考虑到处理可能含有 null 值的列时。

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)>

练习题

统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品。

代码：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	p.product_name,
	sp.quantity 
FROM
	shopproduct sp
	RIGHT OUTER JOIN product p ON sp.product_id = p.product_id;

结果：

外连结要点:

①使用 left 或 right 来指定主表

②最终的结果会显示主表中的所有记录及其字段信息。对于那些在另一个表中没有匹配到的记录，相应表的字段值将以 NULL 填充。

 4.2.2.1 结合 WHERE 子句

练习题:

使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。（注意高压锅和圆珠笔两种商品在所有商店都无货，所以也应该包括在内。）

代码：

-- 使用 or 子句
SELECT
	p.product_id,
	p.product_name,
	p.sale_price,
	sp.shop_id,
	sp.shop_name,
	sp.quantity 
FROM
	shopproduct sp
	RIGHT JOIN product p ON sp.product_id = p.product_id 
WHERE
	sp.quantity < 50 
	OR quantity IS NULL 
ORDER BY
	ISNULL( sp.shop_id ),
	sp.shop_id;-- 使用子查询先筛选出数量小于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 ( SELECT shop_id, shop_name, product_id, quantity FROM shopproduct WHERE quantity < 50 ) AS sp ON p.product_id = sp.product_id 
ORDER BY
	ISNULL( shop_id ),
	shop_id;

4.2.2.2 全外连结

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

 （MySQL8.0 目前还不支持全外连结，但可以对左连结和右连结的结果进行 UNION 来实现全外连结。）

4.2.3 多表连结

4.2.3.1 多表进行内连结

根据 inventoryproduct 表及 ShopProduct 表和 Product 表，使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少。（使用 inner join 添加即可）

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	p.product_name,
	p.sale_price,
	IP.inventory_quantity 
FROM
	shopproduct AS sp
	INNER JOIN product AS p ON sp.product_id = p.product_id
	INNER JOIN inventoryproduct AS IP ON sp.product_id = IP.product_id 
WHERE
	IP.inventory_id = 'P001';

结果：

4.2.3.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 shopproduct sp ON p.product_id = sp.product_id
	LEFT OUTER JOIN inventoryproduct IP ON sp.product_id = IP.product_id;
my_rank;

结果：

4.2.4 ON 子句进阶——非等值连结

除了使用相等判断的等值连结，也可以使用比较运算符来进行连接。

下面使用非等值自左连结（self join）实现排名。

练习题:

希望对 Product 表中的商品按照售价赋予排名。一个从集合论出发，使用自左连结的思路是， 对每一种商品，找出售价不低于它的所有商品，然后对售价不低于它的商品使用 COUNT 函数计数。

 代码：

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_name 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;

结果：

注意：

① COUNT 函数的参数是列名时，会忽略该列中的缺失值，参数为 * 时则不忽略缺失值。

②上述排名方案存在一些问题——如果两个商品的价格相等，则会导致两个商品的排名错误，例如，叉子和打孔器的排名应该都是第六，但上述查询导致二者排名都是第七。试修改上述查询使得二者的排名均为第六。

-- 修改后的代码，使用专门用于排名的窗口函数
SELECT
	product_id,
	product_name,
	sale_price,
	DENSE_RANK() OVER ( ORDER BY sale_price DESC ) AS my_rank 
FROM
	product
ORDER BY
	my_rank;

结果：

4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)

两个集合做笛卡尔积，就是使用集合 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;

4.2.6 连结的特定语法和过时语法

使用过时语法的内连结：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	p.product_name,
	p.sale_price 
FROM
	shopproduct sp,
	product p 
WHERE
	sp.product_id = p.product_id 
	AND sp.shop_id = '000A';

不推荐使用理由：

① 使用这样的语法无法马上判断出到底是内连结还是外连结（又或者是其他种类的连结）。

② 由于连结条件都写在 WHERE 子句之中，因此无法在短时间内分辨出哪部分是连结条件，哪部分是用来选取记录的限制条件。

③ 我们不知道这样的语法到底还能使用多久。每个 DBMS 的开发者都会考虑放弃过时的语法，转而支持新的语法。虽然并不是马上就不能使用了，但那一天总会到来的。

本章练习题

4.1

找出 product 和 product2 中售价高于 500 的商品的基本信息。

代码：

SELECT
	p1.* 
FROM
	product p1
	INNER JOIN product2 p2 ON p1.product_id = p2.product_id 
WHERE
	p1.sale_price > 500;

结果：

4.2

借助对称差的实现方式, 求product和product2的交集。

代码：（终于运行出来了，搞了很久TT）

WITH 
full_union AS ( 
SELECT 'p1' AS source, product_id, product_name, sale_price FROM product 
UNION ALL 
SELECT 'p2' AS source, product_id, product_name, sale_price FROM product2),

symmetric_difference AS (
SELECT *
FROM full_union f1
WHERE NOT EXISTS (
SELECT 1
FROM full_union f2
WHERE f2.product_id = f1.product_id AND f2.source != f1.source)),

intersection AS (
SELECT fu.*
FROM full_union fu
WHERE fu.product_id NOT IN (SELECT product_id FROM symmetric_difference)
)

SELECT product_id, product_name, sale_price
FROM intersection i1
UNION 
SELECT product_id, product_name, sale_price
FROM intersection i2;

 结果：

4.3

每类商品中售价最高的商品都在哪些商店有售 ？

代码：

SELECT
	p.product_type,
	p.product_id,
	p.product_name,
	p.max_sale_price,
	sp.shop_id,
	sp.shop_name 
FROM
( SELECT
	product_type,
	product_id,
	product_name,
	MAX( sale_price ) AS max_sale_price 
	FROM
		product 
	GROUP BY
		product_type,
		product_id,
		product_name

) AS p
INNER JOIN shopproduct sp ON sp.product_id = p.product_id
ORDER BY p.product_type;

结果：

4.4

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

-- 使用内连结
SELECT
	p1.product_type,
	p1.product_id,
	p1.product_name,
	p1.sale_price
FROM product p1
INNER JOIN (
	SELECT
		product_type,
		MAX( sale_price ) AS max_sale_price 
	FROM
		product p2 
	GROUP BY
		product_type
) AS p2
ON p1.sale_price = p2.max_sale_price AND p1.product_type = p2.product_type;

-- 关联子查询
SELECT
	p1.product_type,
	p1.product_id,
	p1.product_name,
	p1.sale_price 
FROM
	product p1
WHERE p1.sale_price = (
	SELECT
		MAX( p2.sale_price ) 
	FROM
		product p2 
	WHERE
	p1.product_type = p2.product_type
);

结果：

4.5

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

代码：

SELECT
	p1.product_id,
	p1.product_name,
	p1.sale_price,
	( SELECT SUM( p2.sale_price ) FROM product p2 WHERE p2.sale_price <= p1.sale_price ) AS sum_sale_price 
FROM
	product p1 
ORDER BY
	sale_price ASC;

结果：