【分享】SQL Server连接（JOIN)系列–PART 1

[kinzent]

SQL Server连接（JOIN)介绍

连接在一个关系数据库中是一个非常重要的操作。数据库使用连接从一个表的行来匹配另外一个表的行。例如，我们可以使用连接用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

下面是一个阐释连接所用到的简单脚本：

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的行。

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谓词找不到匹配的行。例如：

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。

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扩展

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，因为这会生成一个超大的结果集，直接搞死服务器。

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相同的结果。

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也是类似的。

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子查询进行求值。

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

  |--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

[jmgh008]

给力啊，顶起

[哥本哈根]

[再来壹串]

[madStone_l]

顶~！ 低调飘过~！

[wanna__be]

[找猫画虎]

现在好像明白了点，链接和关系数据库中讲的关系的意思了，不过链接不像外键那样直接定义而是后来的，又有点像视图，表示还得好好学学。

[喜太狼]

感谢楼主~~

[oreoconansisu]

[mickers]

楼猪是你写的吗，有些错别字，还有后面的半连接什么意思啊，不懂，我都是用left join 的，求解释下后面的两个连接

[pengfaling]

很好！！！！！！！

[zhousq00]

学习了！！！

[fbcnyh]

引用 27 楼 sz_haitao 的回复:

引用 25 楼 a291410855 的回复: outer join 和join有什么区别呢? left outer join 和left join 因为我平时都用left join 没带那个outer left outer join 和left join 一样吧，只是outer可以节省而已 有点疑问的是： from a left join b on a.f1……

left和right的优先级应该是一样的吧.从左到右

[elude]

不错，学习了~~~

[cntigercat]

[hsz790623]

受益匪浅，正在学习sql。。。

[舞台中央的我]

发了 零点的 回帖

[习惯就好]

很清晰明了.

[zhuqiao95]

学习了呵呵。。

[pengfaling]

来学习来了！！！！！！！！！！！！！！

[---涛声依旧---]

楼主辛苦了！！！