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