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

来源:https://www.cnblogs.com/vin-c/archive/2022/06/15/16377793.html
-Advertisement-
Play Games

本文將會和大家一起學習集合運算操作。集合在數學領域表示“(各種各樣的)事物的總和”,在資料庫領域表示記錄的集合。具體來說,表、視圖和查詢的執行結果都是記錄的集合。 本文重點 集合運算就是對滿足同一規則的記錄進行的加減等四則運算。 使用 UNION(並集)、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)。

使用 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;
    

    一定要使用不同數據類型的列時,可以使用 SQL 常用的函數 中的類型轉換函數 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)。

代碼清單 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)。

使用 INTERSECT 選取出表中公共部分的圖示

圖 2 使用 INTERSECT 選取出表中公共部分的圖示

與使用 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 對記錄進行減法運算的圖示

圖 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 如何使用內聯結、外聯結和交叉聯結 末尾的專欄中進行一些簡單的介紹,感興趣的讀者請參考專欄“關係除法”。

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

(完)


  1. 實際上,在有些 DBMS 中,即使數據類型不同,也可以通過隱式類型轉換來完成操作。但由於並非所有的 DBMS 都支持這樣的用法,因此還是希望大家能夠使用恰當的數據類型來進行運算。 ↩︎

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

  3. 只有 Oracle 不使用 EXCEPT,而是使用其特有的 MINUS 運算符。使用 Oracle 的用戶,請用 MINUS 代替 EXCEPT。此外,MySQL 還不支持 EXCEPT,因此也無法使用。 ↩︎


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 一 、通過雲開發平臺快速創建初始化應用 1.創建相關應用模版請參考鏈接:去中心化的前端構建工具 — Vite 2.完成創建後就可以在github中查看到新增的Vite倉庫 二 、 本地編寫 Vite後臺項目最佳起始點 1.將應用模版克隆到本地 首先假定你已經安裝了Git、node,沒有安裝請移步no ...
  • 目錄 一、前景回顧 二、A20地址線 三、全局描述符表 四、CR0寄存器的PE位 五、邁入保護模式 六、測試 一、前景回顧 上回我們說到,保護模式下有著三大特點:地址映射、特權級和分時機制。本來接下來是要向這三點一一發起進攻,不過我們首先需要先邁入保護模式中,不然在實模式下講解保護模式顯得不倫不類。 ...
  • 本文介紹什麼是 SQL 視窗函數,視窗函數可以進行排序、生成序列號等一般的 聚合函數 無法實現的高級操作。 本文重點 視窗函數可以進行排序、生成序列號等一般的聚合函數無法實現的高級操作。 理解 PARTITION BY 和 ORDER BY 這兩個關鍵字的含義十分重要。 一、什麼是視窗函數 視窗函數 ...
  • 1 MySQL安裝 安裝環境:Win10 64位 軟體版本:MySQL 5.7.24 解壓版 1.1 下載 https://downloads.mysql.com/archives/community/ 點開上面的鏈接就能看到如下界面: 選擇選擇和自己系統位數相對應的版本點擊右邊的Download, ...
  • 緩存穿透,緩存雪崩,緩存擊穿 本文整理自黑馬程式員相關資料 緩存穿透 緩存穿透是指客戶端請求的數據在緩存中和資料庫中都部存在,這樣緩存永遠不會生效。因此這些請求都會穿過緩存,最終請求到資料庫上,對資料庫造成非常大的壓力。 常見的解決方案有兩種: 緩存空對象:當緩存和資料庫都沒有請求的數據時,將查詢的 ...
  • 本文介紹 SQL 如何使用內聯結(INNER JOIN)、外聯結(OUTER JOIN)和交叉聯結(CROSS JOIN)。簡單來說,就是將其他表中的列添加過來,進行“添加列”的運算。 本文重點 聯結(JOIN)就是將其他表中的列添加過來,進行“添加列”的集合運算。UNION 是以行(縱向)為單位進 ...
  • 導讀: 本文主要介紹嗶哩嗶哩在數據湖與數據倉庫一體架構下,探索查詢加速以及索引增強的一些實踐。主要內容包括: 什麼是湖倉一體架構 嗶哩嗶哩目前的湖倉一體架構 湖倉一體架構下,數據的排序組織優化 湖倉一體架構下,索引增強與優化的實踐探索 -- 01 什麼是湖倉一體 當我們講湖倉一體時,涉及到數據湖和數 ...
  • 原文鏈接:實時開發平臺建設實踐,深入釋放實時數據價值 視頻回顧:點擊這裡 課件獲取:點擊這裡 一、實時數倉建設背景 隨著整體行業的數字化轉型不斷深入以及技術能力的不斷提高,傳統的 T+1 式(隔日)的離線大數據模式越來越無法滿足新興業務的發展需求,開展實時化的大數據業務,是企業深入挖掘數據價值的一條 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...