SQL 如何進行并集、交集、差集等集合運算

• 一、什么是集合運算
• 二、表的加法——UNION
• 三、集合運算的注意事項
• 四、包含重復行的集合運算——ALL 選項
• 五、選取表中公共部分——INTERSECT
• 六、記錄的減法——EXCEPT

本文將會和大家一起學習集合運算操作，集合在數學領域表示“（各種各樣的）事物的總和”，在資料庫領域表示記錄的集合，具體來說，表、視圖和查詢的執行結果都是記錄的集合，

本文重點

• 集合運算就是對滿足同一規則的記錄進行的加減等四則運算，

• 使用 UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合運算子來進行集合運算，

• 集合運算子可以去除重復行，

• 如果希望集合運算子保留重復行，就需要使用 ALL 選項，

一、什么是集合運算

截至目前，我們已經學習了 從表中讀取資料 以及 插入資料 的方法，

所謂集合運算，就是對滿足同一規則的記錄進行的加減等四則運算，

通過集合運算，可以得到兩張表中記錄的集合或者公共記錄的集合，又或者其中某張表中的記錄的集合，

像這樣用來進行集合運算的運算子稱為集合運算子，

本文將會為大家介紹表的加減法，SQL 如何使用行內結、外聯結和交叉聯結 將會和大家一起學習進行“表聯結”的集合運算子及其使用方法-->，

二、表的加法——UNION

首先為大家介紹的集合運算子是進行記錄加法運算的 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 中執行時，無需用到 ① 的部分（請洗掉），

詳細內容請大家參考 什么是 SQL 事務 中的“創建事務”，

這樣我們的準備作業就完成了，接下來，就讓我們對上述兩張表進行“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），

商品編號為“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;
  

  一定要使用不同資料型別的列時，可以使用 SQL 常用的函式 中的型別轉換函式 CAST，

• 注意事項 ③——可以使用任何 SELECT 陳述句，但 ORDER BY 子句只能在最后使用一次

  通過 UNION 進行并集運算時可以使用任何形式的 SELECT 陳述句，之前學過的 WHERE、GROUP BY、HAVING 等子句都可以使用，

  但是 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），

代碼清單 5 保留重復行

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

執行結果：

法則 2

在集合運算子中使用 ALL 選項，可以保留重復行，

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

下面將要介紹的集合運算子在數的四則運算中并不存在，不過也不難理解，那就是選取兩個記錄集合中公共部分的 INTERSECT（交集） ^[2]，

讓我們趕快來看一下吧，其語法和 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），

與使用 AND 可以選取出一張表中滿足多個條件的公共部分不同，INTERSECT 應用于兩張表，選取出它們當中的公共記錄，

其注意事項與 UNION 相同，我們在“集合運算的注意事項”和“保留重復行的集合運算”中已經介紹過了，希望保留重復行時同樣需要使用 INTERSECT ALL，

六、記錄的減法——EXCEPT

最后要給大家介紹的集合運算子就是進行減法運算的 EXCEPT（差集）^[3]，其語法也與UNION 相同（代碼清單 7），

代碼清單 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 有一點與 UNION 和 INTERSECT 不同，需要注意一下，

那就是在減法運算中減數和被減數的位置不同，所得到的結果也不相同，4 + 2 和 2 + 4 的結果相同，但是 4 - 2 和 2 - 4 的結果卻不一樣，

因此，我們將之前 SQL 中的 Product 和 Product2 互換，就能得到代碼清單 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 所示，

到此，對 SQL 提供的集合運算子的學習已經結束了，

可能有些讀者會想“唉？怎么沒有乘法和除法呢？”關于乘法的相關內容，我們將在 SQL 如何使用行內結、外聯結和交叉聯結 詳細介紹，

此外，SQL 中雖然也存在除法，但由于除法是比較難理解的運算，屬于中級內容，因此我們會在 SQL 如何使用行內結、外聯結和交叉聯結 末尾的專欄中進行一些簡單的介紹，感興趣的讀者請參考專欄“關系除法”，

原文鏈接：https://www.developerastrid.com/sql/sql-union-intersect-except/

（完）

標籤：SQL Server

上一篇：除錯Archery連接SQL Server提示驅動錯誤

下一篇：SQL 如何使用行內結、外聯結和交叉聯結