导航
  • 全部
...

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

kinzent 2012-12-24 09:19:46
加精
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

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


  1. create table Customers (Cust_Id int, Cust_Name varchar(10))

  2. insert Customers values (1, 'Craig')

  3. insert Customers values (2, 'John Doe')

  4. insert Customers values (3, 'Jane Doe')

  5. create table Sales (Cust_Id int, Item varchar(10))

  6. insert Sales values (2, 'Camera')

  7. insert Sales values (3, 'Computer')

  8. insert Sales values (3, 'Monitor')

  9. insert Sales values (4, 'Printer')


Inner joins(内连接)

Inner join是最常见的连接类型。一个inner join只是简单的找到两行,然后根据连接的谓词放在一起。例如,下面的查询使用这样的连接谓词“S.Cust_Id = C.Cust_Id”来找在Sales和Customer表中具有相同Cust_Id的行。



  1. select *

  2. from Sales S inner join Customers C

  3. on S.Cust_Id = C.Cust_Id

  4. Cust_Id Item Cust_Id Cust_Name

  5. ----------- ---------- ----------- ----------

  6. 2 Camera 2 John Doe

  7. 3 Computer 3 Jane Doe

  8. 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. select *

  2. from Sales S left outer join Customers C

  3. on S.Cust_Id = C.Cust_Id

  4. Cust_Id Item Cust_Id Cust_Name

  5. ----------- ---------- ----------- ----------

  6. 2 Camera 2 John Doe

  7. 3 Computer 3 Jane Doe

  8. 3 Monitor 3 Jane Doe

  9. 4 Printer NULL NULL



注意到,SQL Server为对应的“Printer”的销售返回了为NULL的customer数据,因为该sale没有匹配的customer。我们又称这种行为NULL扩展。

使用一个完全外连接,我们可以得到所有的customer,不管他们是否曾经买过东西。以及得到所有的sale,不管他们是否有个合法的customer。

  1. select *

  2. from Sales S full outer join Customers C

  3. on S.Cust_Id = C.Cust_Id

  4. Cust_Id Item Cust_Id Cust_Name

  5. ----------- ---------- ----------- ----------

  6. 2 Camera 2 John Doe

  7. 3 Computer 3 Jane Doe

  8. 3 Monitor 3 Jane Doe

  9. 4 Printer NULL NULL

  10. NULL NULL 1 Craig



下表显示了不同的外连接里哪些行会被保留,哪些行会进行NULL扩展

  1. Join 保留…
  2. -------------------------------------------
  3. A left outer join B all A rows
  4. A right outer join B all B rows
  5. 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. select *

  2. from Sales S cross join Customers C

  3. Cust_Id Item Cust_Id Cust_Name

  4. ----------- ---------- ----------- ----------

  5. 2 Camera 1 Craig

  6. 3 Computer 1 Craig

  7. 3 Monitor 1 Craig

  8. 4 Printer 1 Craig

  9. 2 Camera 2 John Doe

  10. 3 Computer 2 John Doe

  11. 3 Monitor 2 John Doe

  12. 4 Printer 2 John Doe

  13. 2 Camera 3 Jane Doe

  14. 3 Computer 3 Jane Doe

  15. 3 Monitor 3 Jane Doe

  16. 4 Printer 3 Jane Doe


Cross apply

在SQL Server 2005里面引入了cross apply来允许与一个表值函数(TVF:table valued function)进行连接,该TVF有一个在每次执行时都会改变的参数。例如,下列查询会返回与使用inner join相同的结果。

  1. create function dbo.fn_Sales(@Cust_Id int)

  2. returns @Sales table (Item varchar(10))

  3. as

  4. begin

  5. insert @Sales select Item from Sales where Cust_Id = @Cust_Id

  6. return

  7. end

  8. select *

  9. from Customers cross apply dbo.fn_Sales(Cust_Id)

  10. Cust_Id Cust_Name Item

  11. ----------- ---------- ----------

  12. 2 John Doe Camera

  13. 3 Jane Doe Computer

  14. 3 Jane Doe Monitor


我们也可以使用outer apply来找到所有的客户,即使他们没有消费任何的东西。这与outer join也是类似的。

  1. select *

  2. from Customers outer apply dbo.fn_Sales(Cust_Id)

  3. Cust_Id Cust_Name Item

  4. ----------- ---------- ----------

  5. 1 Craig NULL

  6. 2 John Doe Camera

  7. 3 Jane Doe Computer

  8. 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. select *

  2. from Customers C

  3. where exists (

  4. select *

  5. from Sales S

  6. where S.Cust_Id = C.Cust_Id

  7. )

  8. Cust_Id Cust_Name

  9. ----------- ----------

  10. 2 John Doe

  11. 3 Jane Doe


与前面例子不同的是,semi-join会每次返回一个customer.

从执行计划来看,SQL Server确实使用了一个semi-join


  1. |--Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
  2. |--Table Scan(OBJECT:([Customers] AS [C]))
  3. |--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
...全文
给本帖投票
5471 61 打赏 收藏 转发到动态 举报
写回复
用AI写文章
61 条回复
切换为时间正序
请发表友善的回复…
发表回复
---涛声依旧--- 2013-08-01
  • 打赏
  • 举报
回复
楼主辛苦了!!!
pengfaling 2013-07-28
  • 打赏
  • 举报
回复
来学习来了!!!!!!!!!!!!!!
JorZhu 2013-03-08
  • 打赏
  • 举报
回复
学习了呵呵。。
习惯就好 2013-01-10
  • 打赏
  • 举报
回复
很清晰明了.
舞台中央的我 2013-01-09
  • 打赏
  • 举报
回复
发了 零点的 回帖
hsz790623 2013-01-09
  • 打赏
  • 举报
回复
受益匪浅,正在学习sql。。。
cntigercat 2013-01-08
  • 打赏
  • 举报
回复
elude 2013-01-08
  • 打赏
  • 举报
回复
不错,学习了~~~
fbcnyh 2013-01-07
  • 打赏
  • 举报
回复
引用 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的优先级应该是一样的吧.从左到右
zhousq00 2013-01-07
  • 打赏
  • 举报
回复
学习了!!!
pengfaling 2013-01-07
  • 打赏
  • 举报
回复
很好!!!!!!!
mickers 2013-01-06
  • 打赏
  • 举报
回复
楼猪是你写的吗,有些错别字,还有后面的半连接什么意思啊,不懂,我都是用left join 的,求解释下后面的两个连接
oreoconansisu 2013-01-06
  • 打赏
  • 举报
回复
喜太狼 2013-01-06
  • 打赏
  • 举报
回复
感谢楼主~~
找猫画虎 2013-01-05
  • 打赏
  • 举报
回复
现在好像明白了点,链接和关系数据库中讲的关系的意思了,不过链接不像外键那样直接定义而是后来的,又有点像视图,表示还得好好学学。
wanna__be 2013-01-05
  • 打赏
  • 举报
回复
madStone_l 2013-01-05
  • 打赏
  • 举报
回复
顶~! 低调飘过~!
再来壹串 2013-01-04
  • 打赏
  • 举报
回复
哥本哈根 2013-01-04
  • 打赏
  • 举报
回复
jmgh008 2013-01-04
  • 打赏
  • 举报
回复
给力啊,顶起
加载更多回复(33)

34,836

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧

手机看
关注公众号

关注公众号

客服 返回
顶部