1.UNOIN运算符
unoin合并了两个输入查询结果 并消除重复项 简单点说 就是输出并集
SELECT country, region, city FROM HR.EmployeesUNIONSELECT country, region, city FROM Sales.Customers;
默认删除 集合重复项 UNION DISTINCT
UNION ALL运算符 直接合并集合 不去重复项 也就是说 UNION 添加 ALL 会消除sql 的默认检查重复项功能 在不需要重复项功能下 用 UNION ALL 性能更高。
2.INTERSECT
返回两个结果集的交集 返回同时出现的数据 多次重复只返回一条 和UNOIN 一样默认去重复功能
但是 SQL 中并不支持 INTERSECT ALL 的语法
可以通过开窗函数实现这个功能
SELECT ROW_NUMBER() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, cityFROM HR.EmployeesINTERSECTSELECT ROW_NUMBER() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)), country, region, cityFROM Sales.Customers;
其中 SELECT 0 是告诉SQL 不需要做排序开销 仅给数字分配
3.EXCEPT
集合差 集合A-集合B =元素属于A 但不属于B集合
EXCEPT 也是隐式 去除重复项 例：
SELECT country, region, city FROM HR.EmployeesEXCEPTSELECT country, region, city FROM Sales.Customers;
前面的是A集合 except 后面的是B集合 相减就是结果集
同样的 SQL 中并不支持 EXCEPT ALL 的语法 也可以用开窗函数实现
WITH EXCEPT_ALLAS( SELECT ROW_NUMBER() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, city FROM HR.Employees EXCEPT SELECT ROW_NUMBER() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)), country, region, city FROM Sales.Customers)SELECT country, region, cityFROM EXCEPT_ALL;
记住如果要使用多个集合语句 就要注意优先级问题
INTERSECT 是优先于EXCEPT,UNION的 例子
SELECT country, region, city FROM Production.Suppliers as aEXCEPTSELECT country, region, city FROM HR.Employees as bINTERSECTSELECT country, region, city FROM Sales.Customers as c ;
实际上 先是计算出 b和c 的交集 再去 减a 结果式子就是 a-(b N c) 而不是 (a-b) N c