读SQL进阶教程笔记04_集合运算

1. 集合论是SQL语言的根基

1.1. UNION

• 1.1.1. SQL-86标准
  

1.2. NTERSECT和EXCEPT

• 1.2.1. SQL-92标准
  

1.3. 除法运算（DIVIDE BY）

• 1.3.1. 没有被标准化
  

2. 注意事项

2.1. SQL能操作具有重复行的集合，可以通过可选项ALL来支持

• 2.1.1. 不允许重复
  
  
  
  • 2.1.1.1. 直接使用UNION或INTERSECT
    
  • 2.1.1.2. 集合运算符为了排除掉重复行，默认地会发生排序
    
  
  
• 2.1.2. 允许重复
  
  
  
  • 2.1.2.1. 加上可选项ALL
    

    2.1.2.1.1. 不会再排序，所以性能会有提升
    

    2.1.2.1.2. 非常有效的用于优化查询性能的方法
    

    
    
  • 2.1.2.2. UNION ALL
    

    2.1.2.2.1. 不具有幂等性
    

    
    
  
  

2.2. 集合运算符有优先级

• 2.2.1. INTERSECT比UNION和EXCEPT优先级更高
  
• 2.2.2. 括号明确地指定运算顺序
  

2.3. 各个DBMS提供商在集合运算的实现程度上参差不齐

• 2.3.1. SQL Server从2005版开始支持INTERSECT和EXCEPT
  
• 2.3.2. Oracle这样，实现了EXCEPT功能但却命名为MINUS的数据库
  
• 2.3.3. INTERSECT和EXCEPT不能在MySQL里执行
  

2.4. 除法运算没有标准定义

• 2.4.1. 四则运算里的和（UNION）、差（EXCEPT）、积（CROSS JOIN）都被引入了标准SQL
  

3. 检查集合相等性

3.1. “相等”指的是行数和列数以及内容都相同
3.2. “是同一个集合”
3.3. 原理1

• 3.3.1. S UNION S = S
  
• 3.3.2. 幂等性（indempotency）
  
  
  
  • 3.3.2.1. 抽象代数里群论等理论中的概念
    
  • 3.3.2.2. 二目运算符对任意S，都有SS = S成立”
    
  
  

3.4. 示例1

• 3.4.1.
  

1.   SELECT COUNT(＊) AS row_cnt
2.      FROM ( SELECT ＊
3.              FROM tbl_A
4.            UNION
5.            SELECT ＊
6.              FROM tbl_B ) TMP;

复制代码

• 3.4.2. 这个查询的结果与tbl_A及tbl_B的行数一致，则两张表是相等的
  
• 3.4.3. 表tbl_A和表tbl_B的行数是一样的
  
  
  
  • 3.4.3.1. 如果行数不一样，那就不需要比较其他的了
    
  
  

3.5. 原理2

• 3.5.1. 如果A UNION B = A INTERSECT B，则集合A和集合B相等
  
• 3.5.2. (A UNION B) EXCEPT (A INTERSECT B)的结果集是不是空集就可以了
  
• 3.5.3. INTERSECT
  
  
  
  • 3.5.3.1. 幂等性（indempotency）
    
  
  
• 3.5.4. EXCEPT不具有幂等性
  

3.6. 示例2

• 3.6.1. --两张表相等时返回“相等”，否则返回“不相等”
  

1.    SELECT CASE WHEN COUNT(＊) = 0
2.                THEN ’相等’
3.                ELSE’不相等’END AS result
4.      FROM ((SELECT ＊ FROM  tbl_A
5.            UNION
6.            SELECT ＊ FROM  tbl_B)
7.            EXCEPT
8.            (SELECT ＊ FROM  tbl_A
9.            INTERSECT
10.            SELECT ＊ FROM  tbl_B)) TMP;

复制代码

• 3.6.2. 改进版不需要事先查询两张表的行数
  
• 3.6.3. 需要进行4次排序（3次集合运算加上1次DISTINCT）
  
  
  
  • 3.6.3.1. 性能会有所下降
    
  
  

3.7. 示例3

• 3.7.1. --用于比较表与表的diff
  

1.    (SELECT ＊ FROM  tbl_A
2.     EXCEPT
3.     SELECT ＊ FROM  tbl_B)
4.    UNION ALL
5.    (SELECT ＊ FROM  tbl_B
6.     EXCEPT
7.     SELECT ＊ FROM  tbl_A);

复制代码

4. 用差集实现关系除法运算

4.1. 嵌套使用NOT EXISTS
4.2. 使用HAVING子句转换成一对一关系
4.3. 把除法变成减法

• 4.3.1. 示例
  
  
  
  • 4.3.1.1.
    
  
  

• 4.3.1.2. --用求差集的方法进行关系除法运算（有余数）
  

1.    SELECT DISTINCT emp
2.      FROM EmpSkills ES1
3.     WHERE NOT EXISTS
4.            (SELECT skill
5.              FROM Skills
6.            EXCEPT
7.            SELECT skill
8.              FROM EmpSkills ES2
9.              WHERE ES1.emp = ES2.emp);

复制代码

• 4.3.1.3. 关联子查询是为了使SQL能够实现类似面向过程语言中循环的功能而引入的
  

5. 寻找相等的子集

5.1. IBM过去研制的第一个关系数据库实验系统——System R

• 5.1.1. 用CONTAINS这一谓词来检查集合间的包含关系
  
• 5.1.2. 后来因为性能原因被删除掉了，直到现在也没有恢复
  
• 5.1.3.
  

1. SELECT 'A CONTAINS B'
3.      FROM SupParts
4.     WHERE (SELECT part
5.              FROM SupParts
6.            WHERE  sup ='A')
7.              CONTAINS
8.                  (SELECT part
9.                    FROM SupParts
10.                    WHERE  sup ='B')

复制代码

5.2. 示例

• 5.2.1. --生成供应商的全部组合
  

1.    SELECT SP1.sup AS s1, SP2.sup AS s2
2.      FROM SupParts SP1, SupParts SP2
3.     WHERE SP1.sup ＜ SP2.sup
4.     GROUP BY SP1.sup, SP2.sup;

复制代码

• 5.2.2.
  

1. SELECT SP1.sup AS s1, SP2.sup AS s2
3.      FROM SupParts SP1, SupParts SP2
4.     WHERE SP1.sup ＜ SP2.sup                  --生成供应商的全部组合
5.      AND SP1.part = SP2.part                --条件1：经营同种类型的零件
6.     GROUP BY SP1.sup, SP2.sup
7.    HAVING COUNT(＊) = (SELECT COUNT(＊)       --条件2：经营的零件种类数相同
8.                        FROM SupParts SP3
9.                        WHERE SP3.sup = SP1.sup)
10.      AND COUNT(＊) = (SELECT COUNT(＊)
11.                        FROM SupParts SP4
12.                        WHERE SP4.sup = SP2.sup);

复制代码

• 5.2.3. SQL在比较两个集合时，并不是以行为单位来比较的，而是把集合当作整体来处理的
  

6. 用于删除重复行的高效SQL

6.1. --删除重复行：使用关联子查询

1.    DELETE FROM Products
2.     WHERE rowid ＜ ( SELECT MAX(P2.rowid)
3.                      FROM Products P2
4.                      WHERE Products.name  = P2. name
5.                        AND Products.price = P2.price ) ;

复制代码

6.2. --用于删除重复行的高效SQL语句(1)：通过EXCEPT求补集

1.    DELETE FROM Products
2.     WHERE rowid IN ( SELECT rowid           --全部rowid
3.                        FROM Products
4.                      EXCEPT                 --减去
5.                      SELECT MAX(rowid)     --要留下的rowid
6.                        FROM Products
7.                      GROUP BY name, price) ;

复制代码

6.3. --删除重复行的高效SQL语句(2)：通过NOT IN求补集

1.    DELETE FROM Products
2.     WHERE rowid NOT IN ( SELECT MAX(rowid)
3.                            FROM Products
4.                          GROUP BY name, price);

复制代码

• 6.3.1. 不支持EXCEPT的数据库也可以使用
  

6.4. 实现了行ID的数据库只有Oracle和PostgreSQL

• 6.4.1. PostgreSQL里的相应名字是oid，如果要使用，需要事先在CREATE TABLE的时候指定可选项WITH OIDS
  
• 6.4.2. 如果其他数据库想要使用这些SQL，则需要在表中创建类似的具有唯一性的“id”列
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！