Task04：集合运算

最新推荐文章于 2025-02-20 00:26:55 发布

Jaqen Den

最新推荐文章于 2025-02-20 00:26:55 发布

阅读量334

点赞数

文章标签： sql

本文链接：https://blog.youkuaiyun.com/weixin_46402229/article/details/111488324

版权

集合运算

在标准 SQL 中, 分别对检索结果使⽤ UNION , INTERSECT, EXCEPT 来将检索结果进⾏并,交和差运算, 像 UNION , INTERSECT , EXCEPT 这种⽤来进⾏集合运算的运算符称为集合运算符.
表的加法–UNION
UNION 等集合运算符通常都会除去重复的记录.
使⽤ UNION 对两个查询结果取并集, 和在⼀个查询中使⽤ WHERE ⼦句, 然后使⽤ OR 谓词连接两个查询条件, 能够得到相同的结果.
那么是不是就没必要引⼊ UNION 了呢? 当然不是这样的. 确实, *对于同⼀个表的两个不同的筛选结果集, 使⽤ UNION 对两个结果集取并集, 和把两个⼦查询的筛选条件⽤ OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在⼀起, 就不得不使⽤ UNION 了.*⽽且, 即便是对于同⼀张表, 有时也会出于查询效率⽅⾯的因素来使⽤ UNION

包含重复⾏的集合运算 UNION ALL
.
隐式类型转换：通常来说, 我们会把类型完全⼀致, 并且代表相同属性的列使⽤ UNION 合并到⼀起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在⼀列⾥显示, 例如字符串和数值类型
但是目前MYSQL并不支持intersect和except

借助并集和差集迂回实现交集运算 INTERSECT通过观察集合运算的⽂⽒图, 我们发现, 两个集合的交可以看作是两个集合的并去掉两个集合的对称差.

SELECT * FROM product.product1 where product_id not in (select product_id from product2) ;

不过使⽤ NOT IN 谓词, 基本上可以实现和 SQL 标准语法中的 EXCEPT 运算相同的效果.

EXCEPT ALL 与 bag 的差
类似于 UNION ALL, EXCEPT ALL 也是按出现次数进⾏减法, 也是使⽤ bag 模型进⾏运算.对于两个 bag, 他们的差运算会按照:
1.该元素是否属于作为被减数的 bag,
2.该元素在两个 bag 中的出现次数
这两个⽅⾯来进⾏计算. 只有属于被减数的 bag 的元素才参与 EXCEP ALL 运算, 并且差 bag中的次数,等于该元素在两个 bag 的出现次数之差(差为零或负数则不出现). 因此对于 A ={1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}.

对称差
两个集合 A,B 的对称差是指那些仅属于 A 或仅属于 B 的元素构成的集合.
对称差也是个⾮常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差: ⾸先使⽤ UNION 求两个表的并集, 然后使⽤ INT ERSECT 求两个表的交集, 然后⽤并集减去交集, 就得到了对称差.
但由于在 MySQL 8.0 ⾥, 由于两个表或查询结果的并不能直接求出来,：两个集合的对称差等于A-B 并上 B-A, 因此实践中可以⽤这个思路来求对称差.

1 -- 使用 NOT IN 实现两个表的差集
2 SELECT *
3 FROM Product
4 WHERE product_id NOT IN (SELECT product_id FROM Product2)
5 UNION
6 SELECT *
7 FROM Product2
8 WHERE product_id NOT IN (SELECT product_id FROM Product)

连结(JOIN)

连结( JOIN)就是使⽤某种关联条件(⼀般是使⽤相等判断谓词"="), 将其他表中的列添加过来,进⾏“添加列”的集合运算.

内连结(INNER JOIN)FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

1 SELECT SP.shop_id
2 ,SP.shop_name
3 ,SP.product_id
4 ,P.product_name
5 ,P.product_type
6 ,P.sale_price
7 ,SP.quantity
8 FROM ShopProduct AS SP
9 INNER JOIN Product AS P
10 ON SP.product_id = P.product_id;

要点⼀: 进⾏连结时需要在 FROM ⼦句中使⽤多张表.
要点⼆:必须使⽤ ON ⼦句来指定连结条件.
要点三: SELECT ⼦句中的列最好按照 表名.列名 的格式来使⽤.
此外, ⼀种不是很常⻅的做法是, 还可以将 WHERE ⼦句中的条件直接添加在 ON ⼦句中, 这时候 ON ⼦句后最好⽤括号将连结条件和筛选条件括起来.

另外, 先连结再筛选的标准写法的执⾏顺序是, 两张完整的表做了连结之后再做筛选, 如果要连结多张表, 或者需要做的筛选⽐较复杂时, 在写 SQL 查询时会感觉⽐较吃⼒. 在结合 WHERE⼦句使⽤内连结的时候, 我们也可以更改任务顺序, 并采⽤任务分解的⽅法, **先分别在两个表使⽤ WHERE 进⾏筛选, 然后把上述两个⼦查询连结起来.**如下所示

1 SELECT SP.shop_id
2 ,SP.shop_name
3 ,SP.product_id
4 ,P.product_name
5 ,P.product_type
6 ,P.sale_price
7 ,SP.quantity
8 FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
9 SELECT *
10 FROM ShopProduct
11 WHERE shop_name = '东京' ) AS SP
12 INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
13 (SELECT *
14 FROM Product
15 WHERE product_type = '衣服') AS P
16 ON SP.product_id = P.product_id;

⾃然连结(NATURAL JOIN)
⾃然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的⼀种特例–当两个表进⾏⾃然连结时, 会按照两个表中都包含的列名来进⾏等值内连结, 此时⽆需使⽤ ON 来指定连接条件.SELECT * FROM shopproduct NATURAL JOIN Product

外连结(OUTER JOIN)
内连结会丢弃两张表中不满⾜ ON 条件的⾏, 和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留⽆法匹配到的⾏.
按照保留的⾏位于哪张表, 外连结有三种形式: 左连结, 右连结和全外连结.
左连结会保存左表中⽆法按照 ON ⼦句匹配到的⾏, 此时对应右表的⾏均为缺失值; FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
右连结则会保存右表中⽆法按照 ON ⼦句匹配到的⾏, 此时对应左表的⾏均为缺失值; FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
⽽全外连结则会同时保存两个表中⽆法按照 ON ⼦句匹配到的⾏, 相应的另⼀张表中的⾏⽤缺失值填充.FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

外连结要点 1: 选取出单张表中全部的信息
外连结要点 2:使⽤ LEFT 、RIGHT 来指定主表.
遗憾的是, MySQL8.0 ⽬前还不⽀持全外连结, 不过我们可以对左连结和右连结的结果进⾏UNION 来实现全外连结.

多表连结

1 SELECT SP.shop_id
2 ,SP.shop_name
3 ,SP.product_id
4 ,P.product_name
5 ,P.sale_price
6 ,IP.inventory_quantity
7 FROM ShopProduct AS SP
8 INNER JOIN Product AS P
9 ON SP.product_id = P.product_id
10 INNER JOIN InventoryProduct AS IP
11 ON SP.product_id = IP.product_id
12 WHERE IP.inventory_id = 'P001';

ON ⼦句进阶–⾮等值连结
在刚开始介绍连结的时候, 书上提到过, 除了使⽤相等判断的等值连结, 也可以使⽤⽐较运算符来进⾏连接. 实际上, 包括⽐较运算符(<,<=,>,>=, BET WEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON ⼦句内作为连结条件.

交叉连结—— CROSS JOIN(笛卡尔积)
在连结去掉 ON ⼦句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结⼜叫笛卡尔积, 后者是⼀个数学术语. 两个集合做笛卡尔积, 就是使⽤集合 A 中的每⼀个元素与集合 B 中的每⼀个元素组成⼀个有序的组合. 数据库表(或者⼦查询)的并,交和差都是在纵向上对表进⾏扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, ⽽交叉连接(笛卡尔积)则是在横向上对表进⾏扩张, 即增加新的列, 这⼀点和连结的功能是⼀致的.但因为没有了ON⼦句的限制, 会对左表和右表的每⼀⾏进⾏组合, 这经常会导致很多⽆意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有⼀些⽤处.

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

1 SELECT SP.shop_id
2 ,SP.shop_name
3 ,SP.product_id
4 ,P.product_name
5 ,P.sale_price
6 FROM ShopProduct AS SP
7 CROSS JOIN Product AS P
8 WHERE SP.product_id = P.product_id;

以上的连接方式是在sql server中学习时使用的条件。但是现在已经被视为是过时的语法。
第⼀,使⽤这样的语法⽆法⻢上判断出到底是内连结还是外连结（⼜或者是其他种类的连结）.
第⼆,由于连结条件都写在 WHERE ⼦句之中,因此⽆法在短时间内分辨出哪部分是连结条件,哪部分是⽤来选取记录的限制条件.
第三,我们不知道这样的语法到底还能使⽤多久.每个 DBMS 的开发者都会考虑放弃过时的语法,转⽽⽀持新的语法.虽然并不是⻢上就不能使⽤了,但要提前适应。