SQL：七集合运算

表的加减法

什么是集合运算

集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合
表、视图和查询的执行结果都是记录的集合
集合运算，就是对满足同一规则的记录进行的加减等四则运算
进行集合运算的运算符称为集合运算符

表的加法

UNION（并集）
集合运算符会除去重复的记录

-- 使用UNION对表进行加法运算 
SELECT product_id, product_name  
FROM Product 
UNION 
SELECT product_id, product_name  
FROM Product2;

集合运算的注意事项

①作为运算对象的记录的列数必须相同
②作为运算对象的记录中列的类型必须一致
③可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

-- 列数不一致时会发生错误 
SELECT product_id, product_name  
FROM Product 
UNION 
SELECT product_id, product_name, sale_price  
FROM Product2;

-- 数据类型不一致时会发生错误 
SELECT product_id, sale_price  
FROM Product 
UNION 
SELECT product_id, regist_date  
FROM Product2;

-- ORDER BY子句只在最后使用一次 
SELECT product_id, product_name
FROM Product 
WHERE product_type = '厨房用具' 
UNION 
SELECT product_id, product_name  
FROM Product2 
WHERE product_type = '厨房用具' 
ORDER BY product_id;

包含重复行的集合运算

ALL选项
在集合运算符中使用ALL选项，可以保留重复行

-- 保留重复行 
SELECT product_id, product_name  
FROM Product 
UNION ALL 
SELECT product_id, product_name  
FROM Product2;

选取表中公共部分

INTERSECT（交集）
INTERSECT 应用于两张表，选取出它们当中的公共记录

-- 使用INTERSECT选取出表中公共部分
SELECT product_id, product_name  
FROM Product 
INTERSECT 
SELECT product_id, product_name  
FROM Product2 
ORDER BY product_id;

记录的减法

EXCEPT（差集）
减法运算中减数和被减数的位置不同，所得到的结果也不相同

-- 使用EXCEPT对记录进行减法运算
SELECT product_id, product_name  
FROM Product 
EXCEPT 
SELECT product_id, product_name  
FROM Product2 
ORDER BY product_id;

联结（以列为单位对表进行联结）

什么是联结

联结（JOIN）运算
将其他表中的列添加过来，进行“添加列”的运算
实际上，期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表（3 张以上的表也没关系）中选取数据了

内联结

INNER JOIN
所谓联结运算，以A中的列作为桥梁，将 B中满足同样条件的列汇集到同一结果之中
内联结要点
- ①FROM子句
  - FROM ShopProduct AS SP INNER JOIN Product AS P
  - 进行联结时需要在FROM子句中使用多张表
- ②ON子句
  - ON SP.product_id = P.product_id
  - 在 ON 之后指定两张表联结所使用的列（联结键）
  - 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间
- ③SELECT子句
  - SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  - 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写
内联结和WHERE子句结合使用
- 联结运算将满足相同规则的表联结起来时，WHERE、 GROUP BY、HAVING、ORDER BY 等工具都可以正常使用
- 将联结之后的结果想象为新创建出来的一张表，对这张表使用 WHERE 子句等工具
- 这张“表”只在 SELECT 语句执行期间存在，SELECT 语句执行之后就会消失。如果希望继续使用这张“表”，将它创建成视图

-- 将两张表进行内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price  
FROM ShopProduct AS SP INNER JOIN Product AS P 
ON SP.product_id = P.product_id;

-- 内联结和WHERE子句结合使用
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price  
FROM ShopProduct AS SP INNER JOIN Product AS P 
ON SP.product_id = P.product_id 
WHERE SP.shop_id = '000A';

外联结

OUTER JOIN
外联结要点
- ①选取出单张表中全部的信息
- ②每张表都是主表吗
  - 把哪张表作为主表，最终的结果中会包含主表内所有的数据
  - 指定主表的关键字是 LEFT 和 RIGHT
    - 使用LEFT 时FROM 子句中写在左侧的表是主表，使用RIGHT 时右侧的表是主表
3张以上的表的联结
交叉联结
- CROSS JOIN（笛卡儿积）
  - 对满足相同规则的表进行交叉联结的集合运算符
- 进行交叉联结时无法使用内联结和外联结中所使用的ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积
联结的特定语法和过时语法

对于联结的过时语法和特定语法，虽然不建议使用，但还是希望能够读懂。

-- 将两张表进行外联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,  P.sale_price  
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P 
ON SP.product_id = P.product_id;

-- 对3张表进行内联结
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';

-- 将两张表进行交叉联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name  FROM ShopProduct AS SP CROSS JOIN Product AS P;

-- 使用过时语法的内联结
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';

SQL：七 集合运算

表的加减法

什么是集合运算

表的加法

集合运算的注意事项

包含重复行的集合运算

选取表中公共部分

记录的减法

联结（以列为单位对表进行联结）

什么是联结

内联结

外联结

SQL：七集合运算