MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

2022-10-26 14:03:35

前言

最近幾年，MySQL 不斷致力於相容 SQL 標準。例如 MySQL 8.0 中的視窗函數、通用表表示式、檢查約束等等。

最新發布的 MySQL 8.0.31 繼續對 SQL 語句進行了增強，提供了缺失已久的兩個集合操作符：INTERSECT 和 EXCEPT。

交集操作符（INTERSECT）

INTERSECT 操作符用於返回兩個查詢結果中的共同部分，即同時出現在第一個查詢結果和第二個查詢結果中的資料，並且對最終結果進行了去重操作。交集運算的示意圖如下：

其中，1 和 2 是兩個查詢中都存在的資料；因此交集運算的結果只包含 1 和 2。

我們建立一個年度優秀員工表（excellent_emp），用於演示集合操作：

CREATE TABLE excellent_emp(
    year   INT NOT NULL, 
    emp_id INTEGER NOT NULL,
    CONSTRAINT pk_excellent_emp PRIMARY KEY (YEAR, emp_id)
);
 
INSERT INTO excellent_emp VALUES (2018, 9);
INSERT INTO excellent_emp VALUES (2018, 11);
INSERT INTO excellent_emp VALUES (2019, 9);
INSERT INTO excellent_emp VALUES (2019, 20);

以下範例用於查詢 2018 年和 2019 年都是優秀員工的員工編號：

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
INTERSECT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;
 
emp_id|
------|
     9|

其中，INTERSECT 表示交集運算。第一個查詢語句返回了 9 和 11，第二個查詢語句返回了 9 和 20，最終結果返回共同的 9。集合操作返回的欄位名由第一個語句決定，此處兩個語句擁有相同的欄位名（emp_id）。

對於 MySQL 5.7 以及之前的版本，以上範例可以改寫為等價的連線查詢：

SELECT t1.emp_id
  FROM excellent_emp t1
  JOIN excellent_emp t2
    ON (t1.emp_id = t2.emp_id
        AND t1.year = 2018
        AND t2.year = 2019);
 
emp_id|
------|
     9|

交集運算都可以改寫為等價的等值內連線查詢。

INTERSECT 操作符的完整語法如下：

SELECT ...
INTERSECT [ALL | DISTINCT] SELECT ...
[INTERSECT [ALL | DISTINCT] SELECT ...]

ALL 選項表示保留查詢結果集中的重複記錄，DISTINCT 選項表示去除查詢結果集中的重複記錄，預設選項為 DISTINCT。

另外，INTERSECT 操作符的優先順序比 UNION 和 EXCEPT 更高，因此以下兩種寫法等價：

TABLE r EXCEPT TABLE s INTERSECT TABLE t;

TABLE r EXCEPT (TABLE s INTERSECT TABLE t);

關於 INTERSECT 操作符的更多資訊，可以參考官方檔案。

差集操作符（EXCEPT）

EXCEPT 操作符用於返回出現在第一個查詢結果中，但不在第二個查詢結果中的記錄，並且對最終結果進行了去重操作。差集運算的示意圖如下：

第一個查詢的結果中只有 3 沒有出現在第二個查詢的結果中，因此差集運算的結果只保留了 3。

以下語句查詢 2019 年被評為優秀，但是 2018 年不是優秀的員工：

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
EXCEPT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;
 
emp_id|
------|
    20|

查詢結果顯示，只有 20 號員工是 2019 年新晉的優秀員工。

對於 MySQL 5.7 以及之前的版本，以上範例可以改寫為等價左外連線查詢：

SELECT t1.emp_id
  FROM excellent_emp t1
  LEFT JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t2.year = 2018)
 WHERE t1.year = 2019
   AND t2.emp_id IS NULL;
 
emp_id|
------|
    20|

其中，左外連線返回了所有的優秀員工；然後利用 WHERE 條件找出其中 2019 年是優秀但 2018 年不是優秀的員工。

EXCEPT 操作符的完整語法如下：

SELECT ...
EXCEPT [ALL | DISTINCT] SELECT ...
[EXCEPT [ALL | DISTINCT] SELECT ...]

ALL 選項表示保留查詢結果集中的重複記錄，DISTINCT 選項表示去除查詢結果集中的重複記錄，預設選項為 DISTINCT。

關於 INTERSECT 操作符的更多資訊，可以參考官方檔案。

總結

到此這篇關於MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT的文章就介紹到這了,更多相關MySQL8.0 INTERSECT和EXCEPT內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

目錄

前言

交集操作符（INTERSECT）

差集操作符（EXCEPT）

總結

熱門文章