UNION(并集)集合运算
1.UNION ALL集合运算
该集合运算返回在输入的多集中出现的所有行,它实际上不会对行进行比较,也不会删除重复行。假设查询Query1返回m行,查询Query2返回n行,则该集合运算后返回(m+n)行
1 SELECT country, region, city FROMHR.Employees2 UNION ALL
3 SELECT country, region, city FROM Sales.Customers;
2.UNION DISTINCT集合运算
此集合运算如果两个输入集中包含相同的行,则该行在结果中只出现一次,也就是说运算结果是一个真正的集合,而不是多集
1 SELECT country, region, city FROMHR.Employees2 UNION
3 SELECT country, region, city FROM Sales.Customers;
INTERSECT(交集)集合运算
1.INTERSECT DISTINCT集合运算
1 SELECT country, region, city FROMHR.Employees2 INTERSECT
3 SELECT country, region, city FROM Sales.Customers;
2.INTERSECT ALL集合运算
SQL Server不支持内建的INTERSECT ALL运算,那么我们需要另外的解决方案来实现此集合运算。首先先看一段代码
1 SELECT
2 ROW_NUMBER()3 OVER(PARTITION BYcountry, region, city4 ORDER BY (SELECT 0)) ASrownum,5 country, region, city6 FROM HR.Employees
执行结果:
ROW_NUMBER函数是关键,它能给我们提供行号;另外,上面的SQL中ORDER BY后面的SELECT 0是表示查询可以不必在意行的顺序,即不对数据进行排序。分析该查询结果可知,INTERSECT ALL的完整解决方案是:
1 SELECT
2 ROW_NUMBER()3 OVER(PARTITION BYcountry, region, city4 ORDER BY (SELECT 0)) ASrownum,5 country, region, city6 FROMHR.Employees7
8 INTERSECT
9
10 SELECT
11 ROW_NUMBER()12 OVER(PARTITION BYcountry, region, city13 ORDER BY (SELECT 0)),14 country, region, city15 FROM Sales.Customers;
EXCEPT(差集)集合运算
注意,集合A与B的差集(A-B)是由属于集合A,但不属于集合B的元素组成的集合,也就是两集和之差A-B是从A中减去B中也属于A的元素,故差集运算是不对称的,A-B和B-A是不一样的。
1.EXCEPT DISTINCT集合运算
1 SELECT country, region, city FROMHR.Employees2 EXCEPT
3 SELECT country, region, city FROM Sales.Customers;
2.EXCEPT ALL集合运算
SQL Server没有提供内建的此集合运算。解决方案如下:
1 WITHEXCEPT_ALL2 AS
3 (4 SELECT
5 ROW_NUMBER()6 OVER(PARTITION BYcountry, region, city7 ORDER BY (SELECT 0)) ASrownum,8 country, region, city9 FROMHR.Employees10
11 EXCEPT
12
13 SELECT
14 ROW_NUMBER()15 OVER(PARTITION BYcountry, region, city16 ORDER BY (SELECT 0)),17 country, region, city18 FROMSales.Customers19 )20 SELECTcountry, region, city21 FROM EXCEPT_ALL;
集合运算的优先级:INTERSECT比UNION和EXCEPT运算的优先级高,UNION和EXCEPT优先级相等。