SQL Server中的集合運算: UNION, EXCEPT和INTERSECT

天琊藍發表於2020-08-08

SQL Server中的集合運算包括UNION(合併),EXCEPT(差集)和INTERSECT(相交)三種。

集合運算的基本使用

1.UNION(合併兩個查詢結果集,隱式DINSTINCT,刪除重複行)

--合併兩個提取表/派生表(derived table), 返回結果為:[a,b,c,d,e]
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
UNION
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)

2.UNION ALL(簡單合併兩個查詢結果集,不刪除重複行)

--提取表/派生表(derived table)可以是多列,列名、順序可以不同,但列數必須相同
SELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('e','Elina')) Table1 (FC, Name)
UNION ALL
SELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('d','David')) Table2 (FC, Name)

3.EXCEPT(返回出現在第一個結果集但不出現在第二個結果集中的所有行)

--返回結果為:[e]
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
EXCEPT
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)

4.INTERSECT(返回第一個查詢結果集和第二個查詢結果集共有的部分)

--返回結果為:[a,b,c]
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
INTERSECT
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)

集合運算的使用場景

1.使用UNION代替Where子句中的OR,查詢速度更快

--使用Where子句 + OR
SELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000

--使用UNION
SELECT name, population, area FROM world WHERE area > 3000000
UNION
SELECT name, population, area FROM world WHERE population > 25000000

2.使用EXCEPTINTERSECT, 過濾出列表中不存在/存在於資料庫中的項

假設存在表Customers, 資料如下表所示

cust_id cust_name cust_address cust_city cust_state cust_country cust_contact cust_email
1000000001 Village Toys 200 Maple Lane Detroit MI USA John Smith sales@villagetoys.com
1000000002 Kids Place 333 South Lake Drive Columbus OH USA Michelle Green NULL
1000000003 Fun4All 1 Sunny Place Muncie IN USA Jim Jones jjones@fun4all.com
1000000004 Fun4All 829 Riverside Drive Phoenix AZ USA Denise L. Stephens dstephens@fun4all.com
1000000005 The Toy Store 4545 53rd Street Chicago IL USA Kim Howard NULL
--過濾出列表中不存在於資料庫中的項
--返回結果為['1000000006','1000000007']
SELECT [Id] AS [cust_id] FROM
(
VALUES('1000000004'),('1000000005'),('1000000006'),('1000000007')
) dt ([Id])
EXCEPT
SELECT [cust_id] FROM [Customers]

--過濾出列表中存在於資料庫中的項
--返回結果為['1000000004','1000000005']
SELECT [Id] AS [cust_id] FROM
(
VALUES('1000000004'),('1000000005'),('1000000006'),('1000000007')
) dt ([Id])
INTERSECT
SELECT [cust_id] FROM [Customers]
--對於SQLServer 2008以前的版本
SELECT [Id] AS [cust_id] FROM
(
    SELECT '1000000004' UNION ALL
    SELECT '1000000005' UNION ALL
    SELECT '1000000006' UNION ALL
    SELECT '1000000007'
) dt ([Id])
INTERSECT
--EXCEPT
SELECT [cust_id] FROM [Customers]
//使用C#動態生成SQL語句
var list = new List<string>(){"1000000004","1000000005","1000000006","1000000007"};
string sqlQuery = string.Format($@"
    SELECT [Id] AS [cust_id] FROM
    (
        VALUES('{string.Join("'),('", list)}')
    ) dt ([Id]
    INTERSECT
    --EXCEPT
    SELECT [cust_id] FROM [Customers]"
);

更多參考

相關文章