快捷導(dǎo)航

MySQL中的集合運(yùn)算符詳解

更新時(shí)間：2025年02月06日 11:12:02 作者：ZachOn1y

本文主要介紹了MySQL中的集合運(yùn)算符,包括UNION、INTERSECT、EXCEPT等,這些運(yùn)算符用于結(jié)合兩個(gè)或多個(gè)SELECT語(yǔ)句的結(jié)果集,并進(jìn)行去重、合并或差集操作

集合運(yùn)算符

MySQL中的 集合運(yùn)算符（Set operators）主要用于結(jié)合兩個(gè)或多個(gè)SELECT語(yǔ)句的結(jié)果集，這些結(jié)果集應(yīng)該具有相同的列數(shù)和數(shù)據(jù)類型，以便能夠進(jìn)行比較或合并。

需要注意的是，MySQL本身并沒(méi)有直接稱為“Set operators”的特定術(shù)語(yǔ)或一組專門的運(yùn)算符，而是使用了一些類似功能的SQL語(yǔ)句或運(yùn)算符來(lái)實(shí)現(xiàn)集合操作，如 UNION、INTERSECT、EXCEPT 以及 UNION ALL 等。

UNION 和 UNION ALL

UNION 操作符允許將兩個(gè)或多個(gè)查詢結(jié)果集合并為單個(gè)結(jié)果集

UNION DISTINCT 是 SQL 中用于合并多個(gè)查詢結(jié)果集并去除重復(fù)行的操作符。
它將多個(gè) SELECT 語(yǔ)句的結(jié)果按照列的順序合并成一個(gè)結(jié)果集，自動(dòng)去重（即使未顯示地指定 DISTINCT），并將結(jié)果進(jìn)行排序（默認(rèn)為升序）。

UNION 和 UNION ALL 的區(qū)別就在于是否會(huì)自動(dòng)去重且是否自動(dòng)排序

UNION ALL 是 SQL 中用于合并多個(gè)查詢結(jié)果集的操作符。
它將多個(gè) SELECT 語(yǔ)句的結(jié)果按照列的順序合并成一個(gè)結(jié)果集，并保留重復(fù)行，不會(huì)對(duì)結(jié)果集進(jìn)行排序。
執(zhí)行速度要快于UNION。若要進(jìn)行排序，可以使用 ORDER BY 子句。

語(yǔ)法結(jié)構(gòu)：

SELECT column1, column2, ... FROM table1
UNION [DISTINCT | ALL]
SELECT column1, column2, ... FROM table2;
UNION [DISTINCT | ALL]
SELECT column1, column2, ... FROM table3;

每個(gè)SELECT語(yǔ)句表示要合并的結(jié)果集，每個(gè)查詢的列數(shù)和數(shù)據(jù)類型必須相同，若列數(shù)不相同，需要添加NULL來(lái)補(bǔ)足。

缺點(diǎn)

查詢語(yǔ)句太過(guò)冗長(zhǎng)
查詢的性能不太好，因?yàn)閿?shù)據(jù)庫(kù)引擎必須在內(nèi)部執(zhí)行兩個(gè)單獨(dú)的查詢，并將結(jié)果集合并為一個(gè)查詢。

UNION vs JOIN

JOIN 水平地將查詢結(jié)果集結(jié)合，UNION 垂直地將查詢結(jié)果結(jié)合

UNION 結(jié)合別名

若為查詢結(jié)果的列頭設(shè)置別名，需要在第一個(gè)SELECT語(yǔ)句中為字段設(shè)置別名：

SELECT 
    CONCAT(firstName,' ',lastName) fullname
FROM
    employees 
UNION SELECT 
    CONCAT(contactFirstName,' ',contactLastName)
FROM
    customers;

EXCEPT / MINUS

EXCEPT（MINUS） 是 SQL 中用于組合多個(gè)查詢結(jié)果并返回不在第一個(gè)查詢結(jié)果中的唯一行的關(guān)鍵字。通常與 UNION 或 UNION ALL 一起使用，用于執(zhí)行集合間的差集操作

MySQL 8.0.31 以上的版本支持 EXCEPT 關(guān)鍵字，在 MySQL低版本通常使用NOT EXISTS或LEFT JOIN…IS NULL來(lái)模擬

EXCEPT 在不同的數(shù)據(jù)庫(kù)管理系統(tǒng)中的實(shí)現(xiàn)可能會(huì)有所差異。在某些數(shù)據(jù)庫(kù)中，可能會(huì)使用 MINUS 關(guān)鍵字來(lái)執(zhí)行類似的操作

SELECT column_list FROM table1    -- query1
EXCEPT | MINUS [DISTINCT | ALL]
SELECT column_list FROM table2;   -- query2

'''等價(jià)于'''
SELECT column_list FROM table1
LEFT JOIN table2 ON join_predicate
WHERE table2.column_name IS NULL;

EXCEPT/MINUS 將把 query1 的結(jié)果與 query2 的結(jié)果集進(jìn)行比較，并**返回 query1 的結(jié)果集中，且沒(méi)有出現(xiàn)在 query2 的結(jié)果集中的column。

query1 和 query2 中的列的順序和數(shù)量必須相同
query1 和 query2 中對(duì)應(yīng)列的數(shù)據(jù)類型必須相同
默認(rèn)情況下，EXCEPT/MINUS 會(huì)使用 DISTINCT 選項(xiàng)，即使省略了該關(guān)鍵字，它會(huì)自動(dòng)去重。
EXCEPT/MINUS ALL 將會(huì)保留重復(fù)項(xiàng)
EXCEPT/MINUS 將會(huì)返回一個(gè)結(jié)果集，查詢結(jié)果的列名來(lái)自第一個(gè)查詢。如果需要改變列名，可以使用別名
借助 ORDER BY 子句 EXCEPT/MINUS 結(jié)果集進(jìn)行排序

INTERSECT

INTERSECT 是 SQL 中一種用于獲取兩個(gè)查詢結(jié)果集的交集的集合操作符，即同時(shí)存在于所有 SELECT 語(yǔ)句結(jié)果集中的行

MySQL 在其 8.0.31 及更高版本中引入了 SQL 標(biāo)準(zhǔn)中的 INTERSECT 運(yùn)算符，而在低版本中，通常使用 INNER JOIN 或 EXISTS 子句來(lái)模擬INTERSECT，即找出兩個(gè)結(jié)果集的交集。

SELECT column_list FROM table1    -- query1
INTERSECT [DISTINCT | ALL]
SELECT column_list FROM table2    -- query2

INTERSECT操作符比較 query1 和 query2 兩個(gè)查詢的結(jié)果集并返回二者公共行，不同于 UNION 將二者結(jié)合。

query1 和 query2 中的列的順序和數(shù)量必須相同
query1 和 query2 中對(duì)應(yīng)列的數(shù)據(jù)類型必須相同
默認(rèn)情況下，INTERSECT 會(huì)使用 DISTINCT 選項(xiàng)，即使省略了該關(guān)鍵字，它會(huì)自動(dòng)去重。
INTERSECT ALL 將會(huì)保留重復(fù)項(xiàng)
INTERSECT 將會(huì)返回一個(gè)結(jié)果集，查詢結(jié)果的列名來(lái)自第一個(gè)查詢。如果需要改變列名，可以使用別名
借助 ORDER BY 子句 INTERSECT 結(jié)果集進(jìn)行排序