Intersect, Except, Union, All and Any（1）
时间：2010-11-14 来源：alen88
-注：本文为意译，对原文进行了一些改变
在我浏览SQL Server 2008的培训课程时，发现了一些我在平日不太使用的SQL谓词：
view source print?
生成的执行计划如下:
Subtree Cost: 0.615925
2. 使用NOT IN
生成的执行计划如下:
Subtree Cost: 0.614287
3. 使用EXCEPT谓词
生成的执行计划如下:
Subtree Cost: 0.614287
使用EXCEPT谓词和使用NOT IN的子树成本相同，且执行计划也相同。
- Intersect
- Except
- ALL
- ANY
- 在sales territory 10（United Kingdom）中的客户
- 在‘2004-07-01’后生成的订单
| 1 | 1.使用left join |
| 01 | select |
| 02 | C.CustomerID |
| 03 | from |
| 04 | Sales.Customer as C |
| 05 | left join |
| 06 | Sales.SalesOrderHeader as OH |
| 07 | on |
| 08 | C.CustomerID = OH.CustomerID |
| 09 | and |
| 10 | OrderDate >= '2004-07-01' |
| 11 | where |
| 12 | OH.CustomerID is null |
| 13 | and |
| 14 | C.TerritoryID = 10; |
| 01 | select |
| 02 | CustomerID |
| 03 | from |
| 04 | Sales.Customer |
| 05 | where |
| 06 | TerritoryID=10 |
| 07 | and |
| 08 | CustomerID |
| 09 | not in |
| 10 | ( |
| 11 | select customerid |
| 12 | from Sales.SalesOrderHeader |
| 13 | where OrderDate>='2004-07-01' |
| 14 | ) |
| 01 | select |
| 02 | CustomerID |
| 03 | from |
| 04 | Sales.Customer |
| 05 | where |
| 06 | TerritoryID = 10 |
| 07 | EXCEPT |
| 08 | select |
| 09 | customerid |
| 10 | from |
| 11 | Sales.SalesOrderHeader |
| 12 | where |
| 13 | OrderDate >= '2004-07-01' |
相关阅读 更多 +
