前言

最近幾年，MySQL 不斷致力于兼容 SQL 標(biāo)準(zhǔn)。例如 MySQL 8.0 中的窗口函數(shù)、通用表表達(dá)式、檢查約束等等。

最新發(fā)布的 MySQL 8.0.31 繼續(xù)對(duì) SQL 語(yǔ)句進(jìn)行了增強(qiáng)，提供了缺失已久的兩個(gè)集合操作符：INTERSECT 和 EXCEPT。

交集操作符（INTERSECT）

INTERSECT 操作符用于返回兩個(gè)查詢(xún)結(jié)果中的共同部分，即同時(shí)出現(xiàn)在第一個(gè)查詢(xún)結(jié)果和第二個(gè)查詢(xún)結(jié)果中的數(shù)據(jù)，并且對(duì)最終結(jié)果進(jìn)行了去重操作。交集運(yùn)算的示意圖如下：

其中，1 和 2 是兩個(gè)查詢(xún)中都存在的數(shù)據(jù)；因此交集運(yùn)算的結(jié)果只包含 1 和 2。

我們創(chuàng)建一個(gè)年度優(yōu)秀員工表（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 年都是優(yōu)秀員工的員工編號(hào)：

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

其中，INTERSECT 表示交集運(yùn)算。第一個(gè)查詢(xún)語(yǔ)句返回了 9 和 11，第二個(gè)查詢(xún)語(yǔ)句返回了 9 和 20，最終結(jié)果返回共同的 9。集合操作返回的字段名由第一個(gè)語(yǔ)句決定，此處兩個(gè)語(yǔ)句擁有相同的字段名（emp_id）。

對(duì)于 MySQL 5.7 以及之前的版本，以上示例可以改寫(xiě)為等價(jià)的連接查詢(xún)：

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|

交集運(yùn)算都可以改寫(xiě)為等價(jià)的等值內(nèi)連接查詢(xún)。

INTERSECT 操作符的完整語(yǔ)法如下：

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

ALL 選項(xiàng)表示保留查詢(xún)結(jié)果集中的重復(fù)記錄，DISTINCT 選項(xiàng)表示去除查詢(xún)結(jié)果集中的重復(fù)記錄，默認(rèn)選項(xiàng)為 DISTINCT。

另外，INTERSECT 操作符的優(yōu)先級(jí)比 UNION 和 EXCEPT 更高，因此以下兩種寫(xiě)法等價(jià)：

TABLE r EXCEPT TABLE s INTERSECT TABLE t;

TABLE r EXCEPT (TABLE s INTERSECT TABLE t);

關(guān)于 INTERSECT 操作符的更多信息，可以參考官方文檔。

差集操作符（EXCEPT）

EXCEPT 操作符用于返回出現(xiàn)在第一個(gè)查詢(xún)結(jié)果中，但不在第二個(gè)查詢(xún)結(jié)果中的記錄，并且對(duì)最終結(jié)果進(jìn)行了去重操作。差集運(yùn)算的示意圖如下：

第一個(gè)查詢(xún)的結(jié)果中只有 3 沒(méi)有出現(xiàn)在第二個(gè)查詢(xún)的結(jié)果中，因此差集運(yùn)算的結(jié)果只保留了 3。

以下語(yǔ)句查找 2019 年被評(píng)為優(yōu)秀，但是 2018 年不是優(yōu)秀的員工：

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

查詢(xún)結(jié)果顯示，只有 20 號(hào)員工是 2019 年新晉的優(yōu)秀員工。

對(duì)于 MySQL 5.7 以及之前的版本，以上示例可以改寫(xiě)為等價(jià)左外連接查詢(xún)：

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|

其中，左外連接返回了所有的優(yōu)秀員工；然后利用 WHERE 條件找出其中 2019 年是優(yōu)秀但 2018 年不是優(yōu)秀的員工。

EXCEPT 操作符的完整語(yǔ)法如下：

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

ALL 選項(xiàng)表示保留查詢(xún)結(jié)果集中的重復(fù)記錄，DISTINCT 選項(xiàng)表示去除查詢(xún)結(jié)果集中的重復(fù)記錄，默認(rèn)選項(xiàng)為 DISTINCT。

關(guān)于 INTERSECT 操作符的更多信息，可以參考官方文檔。

總結(jié)

到此這篇關(guān)于MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT的文章就介紹到這了,更多相關(guān)MySQL8.0 INTERSECT和EXCEPT內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論