SQL Server中的集合运算: UNION, EXCEPT和INTERSECT示例代码详解

    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]"
    );

    更多参考

    Set Operators – EXCEPT and INTERSECT

    Set Operators – UNION

    到此这篇关于SQL Server中的集合运算: UNION, EXCEPT和INTERSECT的文章就介绍到这了,更多相关SQL Server中的集合运算内容请搜索lingkb以前的文章或继续浏览下面的相关文章希望大家以后多多支持lingkb!