MySQL小白入门04 - 集合运算

1. 表的加减法

表、视图和查询的执行结果都是记录的集合
标准 SQL 中，用集合运算符 UNION, INTERSECT, EXCEPT 进行并，交和差运算

1.1 表的加法 UNION

1）UNION和谓词OR

UNION 等集合运算符通常都会除去重复的记录

• 将两个不同的表中的结果合并在一起，不得不使用 UNION
• 对于同一张表，有时出于查询效率也会用 UNION

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

练习1：假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

SELECT product_id, product_name, product_type, sale_price, purchase_price
  FROM product 
 WHERE sale_price > 1.5 * purchase_price
 UNION
 SELECT product_id, product_name, product_type, sale_price, purchase_price
  FROM product 
 WHERE sale_price < 800;

思考: 如果不使用 UNION 该怎么写查询语句?

SELECT product_id, product_name, product_type, sale_price, purchase_price
  FROM product 
 WHERE sale_price > 1.5 * purchase_price OR sale_price < 800;

练习2：分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品。

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

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

2）包含重复行 UNION ALL

例子：要知道 Product 和 Product2 中所包含的商品种类及每种商品的数量

• 将两个表的商品种类字段选出来,，然后用 UNION ALL不去重合并
• 对两个表的结果按 Product_type 字段分组计数

SELECT product_type, COUNT(product_type) AS count
FROM
(
SELECT *
FROM product
UNION ALL
SELECT *
FROM product2
)
AS newTable
GROUP BY product_type;

练习题：商店决定对product表中利润低于50%或者售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集。

SELECT *
FROM product
WHERE sale_price < 1.5 * purchase_price
UNION ALL
SELECT *
FROM product
WHERE sale_price < 1000;

3）隐式数据类型转换

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

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

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name, sale_price
  FROM product2;

练习题：使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。

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

SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT "ABC", 123, NULL;

1.2 交运算 INNER JOIN / AND

截止到 MySQL 8.0 版本, MySQL 仍不支持 INTERSECT 操作
需要用 inner join 求交集

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

INTERSECT 与 AND 谓词
对于同个表的两个查询结果，交INTERSECT可等价地将两个查询的检索条件用AND谓词连接实现。

练习题：使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示。

SELECT * 
  FROM Product
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500

1.3 表的减法 NOT IN

截止到 MySQL 8.0 版本, MySQL 仍不支持 EXCEPT 操作
可借助 NOT IN 谓词

练习1：找出只存在于Product表但不存在于Product2表的商品。

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

练习2：使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润不低于30%的商品, 结果应该如下表所示。

SELECT *
FROM product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id
						FROM product
						WHERE sale_price < 1.3 * purchase_price);

1.4 对称差

两个集合A、B的对称差指那些仅属于A或仅属于B的元素构成的集合。

• 两个集合的对称差等于 A-B并集B-A-
• 两个集合的交 = 两个集合的并 - 两个集合的对称差

  首先用UNION求两个表的并集, 然后用INTERSECT求两个表的交集, 最后用并集 - 交集 = 对称差

练习题：使用Product表和Product2表的对称差来查询哪些商品只在其中一张表

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