SQL中的left outer join,inner join,right outer join用法详解

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wbbzj2006/article/details/38357959
SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。
连接类型                                                     定义
内连接                              只连接匹配的行
左外连接                          包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
右外连接                          包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行
全外连接                          包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。

SELECT语句的FROM子句可以指定以下几种类型的连接
FROM子句关键字                                          相应的结果集
CROSS JOIN                                      笛卡尔乘积(所有可能的行对)
INNER JOIN                                         仅对满足连接条件的CROSS中的列
LEFT OUTER JOIN                              一个表满足条件的行,和另一个表的所有行

RIGHT OUTER JOIN                           与LEFT相同,但两个表的角色互换

FULL OUTER JOIN                              LEFT OUTER 和 RIGHT OUTER中所有行的超集


创建两张临时表

create table #a(id int);
insert into #a values(1);
insert into #a values(2);
insert into #a values(3);

create table #b(id int);
insert into #b values(1);
insert into #b values(2);


内连连(INNER JOIN  

select * from #a inner join #b on #a.id = #b.id

查询结果:



左外连接(LEFT OUTER JOIN

select * from #a LEFT OUTER JOIN #b on #a.id = #b.id
其他OUTER可以省略

查询结果:

select * from #b LEFT OUTER JOIN #a on #a.id = #b.id
查询结果:


右外连接(RIGHT OUTER JOIN

select * from #b RIGHT OUTER JOIN #a on #a.id = #b.id
查询结果:

select * from #a RIGHT OUTER JOIN #b on #a.id = #b.id




全连接(FULL OUTER JOIN

select * from #a FULL JOIN #b on #a.id = #b.id


select * from #b FULL JOIN #a on #a.id = #b.id
<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAKsAAAB3CAIAAADzdicaAAAJYUlEQVR4nO2dzW7bxhbHz6PcJ/CGRkDrDbptdkGLAHYwLxC02+B6J4cBVKAyMDstDG8ubu3oAlqru8IaoKhjR0UF3VZ1mvbKLkzKlixLFOYuRqL5TUbkcGhy/vhDoChqfMjz0+GHjimoanX6CFXV6rpUGoLHS0D+LTq5sfSICTDzrSIQgDDxtYLczjL3TFWtPnKq3W632+2mU43GwUiEikMAJnRpTFGAwwkAgJCnKRKAsTuM4XCIMJYEhOvTaoD3068goiiJCIBghRNgOGURYBgGuaLkih6e6AhhQ4Tm83kRCLB/qjqdjjXdbrfZxHQ6xZgoCopMf0iC7ZkOmvYlwLUmLNput0sI6XQ6hyc6xgQhnGjrAKzxkq7rBSTAyjqltNlssvSzT97W1lZk+kPyunYNuHYKY4IJaTabGJNG44AZIXwdWwAQZyJI9qEKQkCjcdDtdjudTtsptpXZcVY4Ad5E+hIQOe1LwN9OeY8DDk/0ZTEgBBPS7/f/jhIA2Cfsj64Fgt7INJvNCkKANW2vARgTtrcjV7TROAgiwJXIoM/32jXgyimMycXFxXA4HA6HhNDDE/3wRLfHrCio3+9ffYoAwDsnSPbFCkKA715gOp02m03raCucgPAsJlm+qtUvncKY9Pt9R6EihK0GxQhj0ul0GASXfgIA+7RX3sWC3n55eVkQAvb39wkhbINau1j2aO1rMcaRewGWV9/Pd8hHKrwMVLV63ynvXmBJAKWU0n6/ryCkKIhB4CsAcE34vhQk+/KTyaQgBDxsTUzYoZ9hGI3GAbmirNLu7++HnAu4gPCdts+xP4arqtV7TjECWHEaDofkakUARhQhSinCCsZEQagXJd8EWy8FvcX+tIAEsMpv7futHW0kAfYaEEQAeM4aIiGoavVfnLIIYOknV469mGEY/X4fE6Io6JcoAUDQnJAaYF++OARYR/72ys9OAfZXCiLAVcZ9y7t3maC3ewn42SmWbyv9hKwIQIgi5eLiAmGFEfBzsACAPXoVtLCvxuNxQQjY2tpSFOR6tE8wBY3gAsJ3OmiZcFW1+nunHAQQiohincsMh8Nut9tut9kZwfsAAYBrIugl75KutxSEgDyrqtXPnGJH++y6EPOSAKRQpHQ6HQUpjIAzPwGA77RrDptwLeCdeXt7Kwngq6pWP3WKHeU9f/5cQYiZEbC8IrTaiykKOo0SALiesjn2+a59hOtVSQB3VbX6T1Fi+fY68o3JdXNzIwngq6pW/zHHkgRwl/AOsML4sRIglZYcBPzr3/+RLpvdBHDFbTQacR0/plIJYzQakb9+LIAlAesPIjx5j48AwzC4jh9TqYRhGIbw5EkC1pQkIMcE9GqVSq3HNYiUwpAErCNd1x3PvRs6EwJSCUPXdeHJe3wEXF9fRyyRCQGphHF9fS08eVwImM/Nu+mMh01z8edf/3OMf/5GVd+csQnrOxY2h5tTCYMNIjx5XAiYzc3J3T0Pm+bij49/OsY/01RVe3f3fk+F7eP7yd395PgFqNo7PgGkGAYbRHjyHhkBi8Xiwx8ffTb9MgH2ZHAkIJUw2CDCk8eHgNl8PJnysLlYXHz46Bj/naaq2il7tM/hE0CKYbBBhCevKARMzvdU2D6ejifT0z0VRBHwKWEUmYD72fx2fMfDprkY/P7BMf7p6yfq61M2sTz+UpdzuDmVMNggwpPHhYDp/ezmdsLDprn4bXDBb/wsw2CDCE8eHwKms9HNmIdN0/z1t9/5jZ9lGGwQ4cnjQsDd9N4Y3fLw3DT/++uA3/hZhsEGEZ48LgScS8WW8ORxIWDOU7PZjOv4MZVKGLPZTHjyuBDAvTMVICePCSU8cxwJCP1CJJF0Xf9nrSrcyfMnCVifAAAQTgAASAKEESA8/bIGCCZA1oC8OT4BLQSolZiA0Nxsb8KTL8TXgG8/h6ffSAJs6tUq7HJ5CgQE1ICvP/sH+xNZEBBcA5ovN1kYkgAfyRpQGgICGsWaO/DiOFmHVtRxQEYERB0HlJ6AgDaht9vw4jhZf46sATl01gTIGpA3+xHg3yb0dht2jpL158gakENnTYCsAXmzDwEBjWLH27DzXbIOLVkDcmgfAvg1cslrgjm0HwHcGrnk9wI5tA8B/Bq5ZA3IoX0I4NdWJWtADu17TZCXZA3IobPuEtNFN4el0iKmF5uAmF/zrKGcbLjkYeRkRVKxJEDMCPmxJEDMCPmxJEDMCPlxLAJaaHVvlWQ9IiEb7psvV3/iy28FEhAzjHIR0KtVVom3TaZKwNHuxmqLN19uwueHYgiIH0a5CHDRkORWX3E23NHuxsZuUwgB8cMoOAHBtxN7/1qFnbeJusSiAhJZA+KHUXACgm4m9e61mvBGX5Eb7mh3Aza/OuK8zsnDKCMBydMfSUA26Y/MX5wwik6Ap0corRs8hWy4zNIfnr+YYZSMgOMdSNwfFkHA4dPIf9LIgoDYYRScAFeX2Hfbjh/We7J3lqRLzDeIh7NwAAAQdS4QP4yCE8C1S0z4CqeSv5ysSCrOuktM+Aqnkr+crEgqzrpLTPgKp5K/nKxIKs66S0z4CqeSv5ysSCrOuktM+Aqnkr+crEgqFtAlVgwJzxxHAmJ+zbOGdF2ndCDcyfMnCZAESAIkAaIzJwmQBGRIgNUllvBGQoEE9F5VQKn1bE8rr3p0QGkDwbPWw5Krpw8LeN+SiADrwnB4j0LJCGghWDYGJb2ZVCgBAFYKRRFw+HT1xWDEP5CXjIAHJf09yDACKq9qCFBLcA1Yuflyc+PlD2UlILBL7PyNmux2YuEE9KwsCifgh682ylwDAnuEYPWDjAl6hMIIoIMWKwNCCTja3Sj7cUDwj07afpWTDwGUNpC9GIjcC5S5UzT4JwdP91R17zxJj1AUAYNeTamgZ6un39cqfucI3Akod7e4i4DTPRW2344n07HtNxn5EUBpA9nOC1rW4SEd0NYzQA3/fKdBgPyPkcB7iVmNYklaxFiXWAwCBr2a8nBmSL9f3dfaebpo7+dCDZ85a9UA63pAeKdawQng2iUW5zPK2/KaYBQBPLvEhKdfEhBNANcuMeHplwREE8C1S0x4+iUB0QRw7RITnn5JQDQBUpESnjaOBAgPSDpjSwLKbklA2S0JKLslAWW3JKDslgSU3ZKAslsSUHZLAspuSUDZLQkouyUBZbckoOyWBJTdkoCyWxJQdksCym5JQNktCSi7/w8d/hs2jSzOsgAAAABJRU5ErkJggg==" alt="" />

交叉连接(CROSS JOIN)

select * from #a CROSS JOIN #b 








阅读更多
换一批

没有更多推荐了,返回首页