集合運算符

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

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

UNION 和 UNION ALL

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

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

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

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

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

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

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

缺點

• 查詢語句太過冗長
• 查詢的性能不太好，因為數(shù)據(jù)庫引擎必須在內(nèi)部執(zhí)行兩個單獨的查詢，并將結(jié)果集合并為一個查詢。

UNION vs JOIN

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

UNION 結(jié)合別名

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

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

EXCEPT / MINUS

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

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

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

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

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

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

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

INTERSECT

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

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

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

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

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

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論