这次我们的目标是要得到在英国(TerritoryID=10)且在‘2004-07-01’后有订单的客户ID。我们将使用多种方法来实现INTERSECT,并比较性能。
1. 首先,我们可以使用INNER JOIN
方法一
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select distinct
C.CustomerID
from
Sales.Customer as C
inner join
Sales.SalesOrderHeader as OH
on
C.CustomerID = OH.CustomerID
where
C.TerritoryID = 10
and
OH.OrderDate >= ' 2004-07-01 ' ;
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select distinct
C.CustomerID
from
Sales.Customer as C
inner join
Sales.SalesOrderHeader as OH
on
C.CustomerID = OH.CustomerID
where
C.TerritoryID = 10
and
OH.OrderDate >= ' 2004-07-01 ' ;
生成的执行计划如下:
其Subtree Cost: 0.614283
方法二
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
C.CustomerID
from
Sales.Customer as C
inner join
( select distinct
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 ' ) as OH
on
C.CustomerID = OH.CustomerID
where
C.TerritoryID = 10
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
C.CustomerID
from
Sales.Customer as C
inner join
( select distinct
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 ' ) as OH
on
C.CustomerID = OH.CustomerID
where
C.TerritoryID = 10
生成的执行计划如下:
其Subtree Cost: 0.615594
2. 我们使用IN谓词实现查询
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
CustomerID
from
Sales.Customer
where
TerritoryID = 10
and
CustomerID in
(
select
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 '
)
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
CustomerID
from
Sales.Customer
where
TerritoryID = 10
and
CustomerID in
(
select
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 '
)
生成的执行计划如下:
其Subtree Cost: 0.614191
3. 最后我们使用INTERSECT谓词
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
CustomerID
from
Sales.Customer
where
TerritoryID = 10
intersect
select
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 '
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> select
CustomerID
from
Sales.Customer
where
TerritoryID = 10
intersect
select
CustomerID
from
Sales.SalesOrderHeader
where
OrderDate >= ' 2004-07-01 '
其生成的执行计划和Subtree Cost和使用IN谓词的查询相同。
对INTERSECT和EXCEPT试用的感受
从上例的试用中看不出特别推荐INTERSECT和EXCEPT的理由,但这两个谓词会方便那些习惯使用集合逻辑思维的人。
给我的另一个感受是,使用不同的query实现方式并进行比较,来发现最适合当前场景的查询是很有好处的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-663348/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-663348/