来自：http://bbs.csdn.net/topics/390326529
连接在一个关系数据库中是一个非常重要的操作。数据库使用连接从一个表的行来匹配另外一个表的行。例如，我们可以使用连接用customers或books或authors来匹配sales。如果没有连接，我们们可能只拥有一个sales和customers 或books或authors的清单，也就无从得知哪个谷歌买了哪个作者写的哪本书。
我们可以通过在FROM子句后面列出所要连接的表来进行连接。我们也可以通过使用不同的子查询来连接两个表。最后，SQL Server可能在优化过程中也会因为不同原因而引入连接。
本文是介绍SQL Server连接的系列文章的第一篇，首先，我们来看看在SQL Server里支持的逻辑连接操作有哪些。它们分别如下：
Inner join
Outer join
Cross join
Cross apply
Semi-join
Anti-semi-join
下面是一个阐释连接所用到的简单脚本：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
create
table
Customers (Cust_Id
int
, Cust_Name
varchar
(10))
insert
Customers
values
(1,
'Craig'
)
insert
Customers
values
(2,
'John Doe'
)
insert
Customers
values
(3,
'Jane Doe'
)
create
table
Sales (Cust_Id
int
, Item
varchar
(10))
insert
Sales
values
(2,
'Camera'
)
insert
Sales
values
(3,
'Computer'
)
insert
Sales
values
(3,
'Monitor'
)
insert
Sales
values
(4,
'Printer'
)
|
Inner joins（内连接）
Inner join是最常见的连接类型。一个inner join只是简单的找到两行，然后根据连接的谓词放在一起。例如，下面的查询使用这样的连接谓词“S.Cust_Id = C.Cust_Id”来找在Sales和Customer表中具有相同Cust_Id的行。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
select
*
from
Sales S
inner
join
Customers C
on
S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
|
注意：
Cust_Id 3 买了两件item，所以该Customer会在结果中出现两次.
Cust_Id 1 没有购买任何东西，所以没在结果中出现。
我们卖了一个‘Printer’ 给 Cust_Id 4。但这边没有这个客户，所以在结果里面也看不到该客户。
Inner Join是中表的位置是可以交换的。也就是说‘A inner join B ‘ 等价于’B inner join A’。
Outer joins（外连接）
假设我们想要看到所有的sales的列表，即使是那些没有匹配的客户。我们可以通过外连接来实现。外连接会保留其中一个或者两个输入表里的所有行，即使我们通过join谓词找不到匹配的行。例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select
*
from
Sales S
left
outer
join
Customers C
on
S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer
NULL
NULL
|
注意到，SQL Server为对应的“Printer”的销售返回了为NULL的customer数据，因为该sale没有匹配的customer。我们又称这种行为NULL扩展。
使用一个完全外连接，我们可以得到所有的customer，不管他们是否曾经买过东西。以及得到所有的sale，不管他们是否有个合法的customer。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
select
*
from
Sales S
full
outer
join
Customers C
on
S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer
NULL
NULL
NULL
NULL
1 Craig
|
下表显示了不同的外连接里哪些行会被保留，哪些行会进行NULL扩展
|
1
2
3
4
5
|
Join
保留…
-------------------------------------------
A
left
outer
join
B
all
A
rows
A
right
outer
join
B
all
B
rows
A
full
outer
join
B
all
A
and
B
rows
|
所有的外连接可以交换的。也就是说，’A left outer join B ‘ 等价于 ‘B right outer join A’。
Cross joins（交叉连接）
一个cross join相当于对两个表执行了一次完全的笛卡尔乘积。也就是说，它会用其中一个表里的每一行来匹配另外一个表的每一行。你无法为cross join用ON子句来指定一个连接谓词。虽然你可以使用where子句来获得与inner join类似的结果。
cross join是相当少见的。两个大表应该完全比较使用cross join，因为这会生成一个超大的结果集，直接搞死服务器。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
select
*
from
Sales S
cross
join
Customers C
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 1 Craig
3 Computer 1 Craig
3 Monitor 1 Craig
4 Printer 1 Craig
2 Camera 2 John Doe
3 Computer 2 John Doe
3 Monitor 2 John Doe
4 Printer 2 John Doe
2 Camera 3 Jane Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer 3 Jane Doe
|
Cross apply
在SQL Server 2005里面引入了cross apply来允许与一个表值函数（TVF:table valued function）进行连接，该TVF有一个在每次执行时都会改变的参数。例如，下列查询会返回与使用inner join相同的结果。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create
function
dbo.fn_Sales(@Cust_Id
int
)
returns
@Sales
table
(Item
varchar
(10))
as
begin
insert
@Sales
select
Item
from
Sales
where
Cust_Id = @Cust_Id
return
end
select
*
from
Customers
cross
apply dbo.fn_Sales(Cust_Id)
Cust_Id Cust_Name Item
----------- ---------- ----------
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor
|
我们也可以使用outer apply来找到所有的客户，即使他们没有消费任何的东西。这与outer join也是类似的。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
select
*
from
Customers
outer
apply dbo.fn_Sales(Cust_Id)
Cust_Id Cust_Name Item
----------- ---------- ----------
1 Craig
NULL
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor
|
Semi-join and Anti-semi-join（半连接和反半连接）
一个semi-join从一个将会与另外一个表连接的表中返回行，只不过没有执行完全的连接。一个anti-semi-join从一个将不会与另外一个表连接的表中返回行，这些行如果我们执行一个外连接的时候将会以NULL进行扩展。
与其他连接操作不同，对一个semi-join并没有显示的语法。但是SQL Server在很多环境中都会使用到semi-join。例如，我们可能使用一个semi-join来对一个EXISTS子查询进行求值。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
select
*
from
Customers C
where
exists (
select
*
from
Sales S
where
S.Cust_Id = C.Cust_Id
)
Cust_Id Cust_Name
----------- ----------
2 John Doe
3 Jane Doe
|
与前面例子不同的是，semi-join会每次返回一个customer.
从执行计划来看，SQL Server确实使用了一个semi-join
|
1
2
3
|
|
--Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
|
--Table Scan(OBJECT:([Customers] AS [C]))
|
--Table Scan(OBJECT:([Sales] AS [S]))
|
对semin-join而言，有left semi-join和right semi-join。一个left semi-join会先从left的输入表中返回与right的表中匹配的行。而right semi-join会从right的输入表中返回匹配left的表的行。
同样，我们可以使用一个anti-semi-join来对一个NOT EXISTS的子查询进行求值。
其他说明
在上面的所有例子里面，我们使用到了一个连接谓词来比较来自不同表的两列的值是否相等。该类join谓词叫做”equijoin”，其他连接谓词（如不相等）也是可能的，但equijoin是相当普遍的，SQL Server在对equijoin进行优化是，比优化其他join时，可以有更多备用的策略。
SQL Server 在优化inner join时，会比优化outer join和其他cross apply有更多的灵活性和算法。也就是说，给定两个查询语句，它们的差别仅在于一个使用inner join，而另外一个使用outer join。SQL Server可能会为仅使用inner join的查询找到一个更好的执行计划。
接下来 …
在后的系列文章中，我们会继续介绍join的其他方面，如逻辑操作符对应的物理连接操作符。
除非注明，本站文章均为原创或编译，转载请注明： 文章来自 sqlpub.net
769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
