SQL UNION（并集）、INTERSECT（交集）、EXCEPT（差集）

• 一、什么是集合運算
• 二、表的加法——UNION
• 三、集合運算的注意事項
• 四、包含重復行的集合運算——ALL 選項
• 五、選取表中公共部分——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），

商品編號為“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 陳述句，之前學過的 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），

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

與使用 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 有一點與 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 聯結 末尾的專欄中進行一些簡單的介紹，感興趣的讀者請參考專欄“關系除法”，

請參閱

• 表的加減法
• SQL 聯結

（完）

標籤：SQL Server

上一篇：SQL CASE 運算式

下一篇：SQL JOIN 以列為單位對表進行聯結