• SQL UNION(并集)、INTERSECT(交集)、EXCEPT(差集)


    学习重点

    • 集合运算就是对满足同一规则的记录进行的加减等四则运算。

    • 使用 UNION(并集)、INTERSECT(交集)、EXCEPT(差集)等集合运算符来进行集合运算。

    • 集合运算符可以去除重复行。

    • 如果希望集合运算符保留重复行,就需要使用ALL选项。

    一、什么是集合运算

    本文将会和大家一起学习集合运算操作集合在数学领域表示“(各种各样的)事物的总和”,在数据库领域表示记录的集合。具体来说,表、视图和查询的执行结果都是记录的集合。

    截至目前,我们已经学习了从表中读取数据以及插入数据的方法。所谓集合运算,就是对满足同一规则的记录进行的加减等四则运算。通过集合运算,可以得到两张表中记录的集合或者公共记录的集合,又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符

    KEYWORD

    • 集合运算

    • 集合

    • 记录的集合

    • 集合运算符

    本文将会为大家介绍表的加减法,下一篇(SQL 联结)将会和大家一起学习进行“表联结”的集合运算符及其使用方法。

    二、表的加法——UNION

    首先为大家介绍的集合运算符是进行记录加法运算的 UNION(并集)

    KEYWORD

    • UNION(并集)

    在学习具体的使用方法之前,我们首先添加一张表,该表的结构与之前我们使用的 Product(商品)表相同,只是表名变为 Product2(商品 2)(代码清单 1)。

    代码清单 1 创建表 Product2(商品 2)

    CREATE TABLE Product2
    (product_id     CHAR(4)      NOT NULL,
     product_name   VARCHAR(100) NOT NULL,
     product_type   VARCHAR(32)  NOT NULL,
     sale_price     INTEGER      ,
     purchase_price INTEGER      ,
     regist_date    DATE         ,
     PRIMARY KEY (product_id));
    

    接下来,我们将代码清单 2 中的 5 条记录插入到 Product2 表中。商品编号(product_id)为“0001”~“0003”的商品与之前 Product 表中的商品相同,而编号为“0009”的“手套”和“0010”的“水壶”是 Product 表中没有的商品。

    代码清单 2 将数据插入到表 Product2(商品 2)中

    SQL Server PostgreSQL

    BEGIN TRANSACTION; ---------①
    INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2009-09-20');
    INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
    INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
    INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
    INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
    COMMIT;
    

    特定的 SQL

    不同的 DBMS 的事务处理的语法也不尽相同。代码清单 2 中的 DML 语句在 MySQL 中执行时,需要将 ① 部分更改为“START TRANSACTION;”。在 Oracle 和 DB2 中执行时,无需用到 ① 的部分(请删除)。

    详细内容请大家参考 事务 中的“创建事务”。

    这样我们的准备工作就完成了。接下来,就让我们对上述两张表进行“Product 表 + Product2 表”的加法计算吧。语法请参考代码清单 3。

    代码清单 3 使用 UNION 对表进行加法运算

    SELECT product_id, product_name
      FROM Product
    UNION
    SELECT product_id, product_name
      FROM Product2;
    

    执行结果

    product_id | product_name
    -----------+-------------
     0001      | T恤衫
     0002      | 打孔器
     0003      | 运动T恤
     0004      | 菜刀
     0005      | 高压锅
     0006      | 叉子
     0007      | 擦菜板
     0008      | 圆珠笔
     0009      | 手套
     0010      | 水壶
    

    上述结果包含了两张表中的全部商品。可能有些读者会发现,这就是我们在学校学过的集合中的并集运算,通过文氏图会看得更清晰(图 1)。

    使用 UNION 对表进行加法(并集)运算的图示

    图 1 使用 UNION 对表进行加法(并集)运算的图示

    商品编号为“0001”~“0003”的 3 条记录在两个表中都存在,因此大家可能会认为结果中会出现重复的记录,但是 UNION 等集合运算符通常都会除去重复的记录。

    法则 1

    集合运算符会除去重复的记录。

    三、集合运算的注意事项

    其实结果中也可以包含重复的记录,在介绍该方法之前,还是让我们先来学习一下使用集合运算符时的注意事项吧。不仅限于 UNION,之后将要学习的所有运算符都要遵守这些注意事项。

    • 注意事项 ① ——作为运算对象的记录的列数必须相同

      例如,像下面这样,一部分记录包含 2 列,另一部分记录包含 3 列时会发生错误,无法进行加法运算。

      -- 列数不一致时会发生错误
      SELECT product_id, product_name
      FROM Product
      UNION
      SELECT product_id, product_name, sale_price
      FROM Product2;
      
    • 注意事项 ②——作为运算对象的记录中列的类型必须一致

      从左侧开始,相同位置上的列必须是同一数据类型。例如下面的 SQL 语句,虽然列数相同,但是第 2 列的数据类型并不一致(一个是数值类型,一个是日期类型),因此会发生错误 [1]

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

      一定要使用不同数据类型的列时,可以使用 各种各样的函数 中的类型转换函数 CAST

    • 注意事项 ③——可以使用任何 SELECT 语句,但 ORDER BY 子句只能在最后使用一次

      通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句,之前学过的 WHEREGROUP BYHAVING 等子句都可以使用。但是 ORDER BY 只能在最后使用一次(代码清单 4)。

      代码清单 4 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;
      

      执行结果

      product_id | product_name
      -----------+--------------
      0004      | 菜刀
      0005      | 高压锅
      0006      | 叉子
      0007      | 擦菜板
      0010      | 水壶
      

    四、包含重复行的集合运算——ALL 选项

    接下来给大家介绍在 UNION 的结果中保留重复行的语法。其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。这里的 ALL 选项,在 UNION 之外的集合运算符中同样可以使用(代码清单 5)。

    KEYWORD

    • ALL 选项

    代码清单 5 保留重复行

    SELECT product_id, product_name
      FROM Product
    UNION ALL
    SELECT product_id, product_name
      FROM Product2;
    

    执行结果

    保留重复行

    法则 2

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

    五、选取表中公共部分——INTERSECT

    下面将要介绍的集合运算符在数的四则运算中并不存在,不过也不难理解,那就是选取两个记录集合中公共部分的 INTERSECT(交集)[2]

    KEYWORD

    • INTERSECT(交集)

    让我们赶快来看一下吧。其语法和 UNION 完全一样(代码清单 6)。

    代码清单 6 使用 INTERSECT 选取出表中公共部分

    Oracle SQL Server DB2 PostgreSQL

    SELECT product_id, product_name
      FROM Product
    INTERSECT
    SELECT product_id, product_name
      FROM Product2
    ORDER BY product_id;
    

    执行结果

     product_id | product_name
    ------------+--------------
     0001       | T恤衫
     0002       | 打孔器
     0003       | 运动T恤
    

    大家可以看到,结果中只包含两张表中记录的公共部分。该运算的文氏图如下所示(图 2)。

    使用 INTERSECT 选取出表中公共部分的图示

    图 2 使用 INTERSECT 选取出表中公共部分的图示

    与使用 AND 可以选取出一张表中满足多个条件的公共部分不同,INTERSECT 应用于两张表,选取出它们当中的公共记录。

    其注意事项与 UNION 相同,我们在“集合运算的注意事项”和“保留重复行的集合运算”中已经介绍过了。希望保留重复行时同样需要使用 INTERSECT ALL

    六、记录的减法——EXCEPT

    最后要给大家介绍的集合运算符就是进行减法运算的 EXCEPT(差集)[3],其语法也与 UNION 相同(代码清单 7)。

    KEYWORD

    • EXCEPT(差集)

    代码清单 7 使用 EXCEPT 对记录进行减法运算

    SQL Server DB2 PostgreSQL

    SELECT product_id, product_name
      FROM Product
    EXCEPT
    SELECT product_id, product_name
      FROM Product2
    ORDER BY product_id;
    

    特定的 SQL

    在 Oracle 中执行代码清单 7 或者代码清单 8 中的 SQL 时,请将 EXCEPT 改为 MINUS

      -- Oracle中使用MINUS而不是EXCEPT
      SELECT …
        FROM …
      MINUS
      SELECT …
        FROM …;
    

    执行结果

    product_id | product_name
    -----------+--------------
     0004      | 菜刀
     0005      | 高压锅
     0006      | 叉子
     0007      | 擦菜板
     0008      | 圆珠笔
    

    大家可以看到,结果中只包含 Product 表中记录除去 Product2 表中记录之后的剩余部分。该运算的文氏图如图 3 所示。

    使用 EXCEPT 对记录进行减法运算的图示

    图 3 使用 EXCEPT 对记录进行减法运算的图示

    EXCEPT 有一点与 UNIONINTERSECT 不同,需要注意一下。那就是在减法运算中减数和被减数的位置不同,所得到的结果也不相同。4 + 22 + 4 的结果相同,但是 4 - 22 - 4 的结果却不一样。因此,我们将之前 SQL 中的 ProductProduct2 互换,就能得到代码清单 8 中的结果。

    代码清单 8 被减数和减数位置不同,得到的结果也不同

    SQL Server DB2 PostgreSQL

    -- 从Product2的记录中除去Product中的记录
    SELECT product_id, product_name
      FROM Product2
    EXCEPT
    SELECT product_id, product_name
      FROM Product
    ORDER BY product_id;
    

    执行结果

     product_id | product_name
    ------------+--------------
     0009       | 手套
     0010       | 水壶
    

    上述运算的文氏图如图 4 所示。

    使用 EXCEPT 对记录进行减法运算的图示(从 Product2 中除去 Product 中的记录)

    图 4 使用 EXCEPT 对记录进行减法运算的图示(从 Product2 中除去 Product 中的记录)

    到此,对 SQL 提供的集合运算符的学习已经结束了。可能有些读者会想“唉?怎么没有乘法和除法呢?”关于乘法的相关内容,我们将在 SQL 联结 中详细介绍。此外,SQL 中虽然也存在除法,但由于除法是比较难理解的运算,属于中级内容,因此我们会在 SQL 联结 末尾的专栏中进行一些简单的介绍,感兴趣的读者请参考专栏“关系除法”。

    请参阅

    (完)


    1. 实际上,在有些 DBMS 中,即使数据类型不同,也可以通过隐式类型转换来完成操作。但由于并非所有的 DBMS 都支持这样的用法,因此还是希望大家能够使用恰当的数据类型来进行运算。 ↩︎

    2. 因为 MySQL 尚不支持 INTERSECT,所以无法使用。 ↩︎

    3. 只有 Oracle 不使用 EXCEPT,而是使用其特有的 MINUS 运算符。使用 Oracle 的用户,请用 MINUS 代替 EXCEPT。此外,MySQL 还不支持 EXCEPT,因此也无法使用。 ↩︎

  • 相关阅读:
    Compiere中的树
    Compiere 模型构建
    LSMW魔鬼教程
    Compiere 因翻译工作没有完成,所以现在系统中的所有帮助去掉
    插入、更新扩展字段
    SAPscript Forms 教程
    SAP ABAP 效率测试
    批量更新数据表
    月份的描述表T247
    SAP 程序下载工具
  • 原文地址:https://www.cnblogs.com/vin-c/p/15607602.html
Copyright © 2020-2023  润新知