在 SELECT 查询中使用集运算符

　　在 SELECT 查询中使用集运算符，可以将来自两个或多个查询的结果合并到单个结果集中。

　　在进行集运算之前，请确保：

（1）所有输入集合中，列数和列的顺序必须相同。

（2）对应的列中，数据类型必须兼容。

 

　　可以在一个查询中使用多个集运算符，运算时基于以下的优先级：

（1）包含在括号中的表达式。

（2）INTERSECT 运算符

（3）从左到右求值的 EXCEPT 和 UNION

 

一、UNION 和 UNION ALL

　　UION 和 UNION ALL 用于合并两个输入查询的结果。如果某一行出现在任一输入集合中，那么它将出现在结果集合中。

　　当对输入集合进行比较时，NULL 被视为相等。

　　ORDER BY 不允许出现在输入集合中，但可以出现在输出结果中。

 

1. UNION ALL

　　UNION ALL 返回所有的输入集合中的所有行，此过程不消除重复的数据行，因此相同的行可以在结果集合中多次出现。

 

SELECT country, region, city FROM Employees

UNION ALL

SELECT country, region, city FROM Customers

UNION ALL

SELECT country, region, city FROM Suppliers ;

 

2. UNION（隐式的DISTINCT）

　　UNION（隐式的DISTINCT）不但组合两个输入集合，而且还将消除重复的数据行。如果某一行出现在两个输入集合中，那么它在结果集合中仅出现一次。由于排除了重复的数据行，因此需要额外的系统开销。

 

SELECT country, region, city FROM Employees

UNION

SELECT country, region, city FROM Customers

ORDER BY country, region, city ;

 

 

二、EXCEPT 和 INTERSECT

　　EXCEPT 和 INTERSECT 比较两个输入查询的结果，返回非重复值。

　　在查询执行计划的图形显示功能中，EXCEPT 运算被显示为 left anti semi join，INTERSECT 运算被显示为 left semi join。

 

1. EXCEPT

　　EXCEPT从左输入集合中返回右输入查询中没有找到的所有非重复的值。

 

SELECT country, region, city FROM Employees

EXCEPT

SELECT country, region, city FROM Customers ;

 

2.INTERSECT

　　INTERSECT返回两个输入集合中都返回的行，而且这些数据行只出现一次（非重复值）。

 

SELECT country, region, city FROM HR.Employees

INTERSECT

SELECT country, region, city FROM Sales.Customers ;

 

 

三、APPLY 运算符

　　APPLY 运算符用在 FROM 子句。它的右边的表通常是一个表表达式（派生表或者是一个内联TVF）。

　　CROSS APPLY 可以在为外部查询中的每行飞速生成数据子集的情况下使用。APPLY 运算与 JOIN 非常相似，不同之处在于 APPLY 的右侧的表表达式可以对来自左侧表的每一行表示一个不同的行集。也就是说，当在右侧使用一个表表达式时，表表达式可以引用左侧表的参数。

 

1. CROSS APPLY

　　CROSS APPLY 允许用户指定根据外部查询中的行构造子集的方式。

SELECT

 *

FROM

 Orders AS O CROSS APPLY

 (

   SELECT TOP(2) * --for each row in Orders there will be at most 2 customers

   FROM Customers C

   WHERE C.[Customer ID] = O.[Customer ID]

   ORDER BY C.[Customer ID]

 ) AS theOrdersTopCustomers

 

2. OUTER APPLY

　　与 CROSS APPLY 不同，OUTER APPLY 还保留外部查询中与内部查询不匹配的行。

SELECT S.supplierid, s.companyname, P.productid, P.productname, P.unitprice

FROM Production.Suppliers AS S

CROSS APPLY

dbo.fn_TopProductsByShipper(S.supplierid) AS P

　　前面的示例显示 OUTER APPLY 结果包括客户 ID“PARIS”，原因是 Orders 表不包含“PARIS”的任何记录。因此，内部查询返回 NULL，原因是 TOP 客户 ID 与 Orders 表中的“PARIS”匹配。将 OUTER 更改为 CROSS 会从结果中排除“PARIS”。