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

SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

连接类型定义

内连接 只连接匹配的行

左外连接 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

右外连接 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

全外连接 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

SELECT语句的FROM子句可以指定以下几种类型的连接

FROM子句关键字 相应的结果集

CROSSJOIN笛卡尔乘积（所有可能的行对）

INNERJOIN仅对满足连接条件的CROSS中的列

LEFT OUTER JOIN一个表满足条件的行，和另一个表的所有行

RIGHTOUTER JOIN与LEFT相同，但两个表的角色互换

FULLOUTER JOIN LEFT OUTER和 RIGHTOUTER中所有行的超集

创建两张临时表

[sql]view plaincopycreatetable#a(idint); insertinto#avalues(1); insertinto#avalues(2); insertinto#avalues(3); createtable#b(idint); insertinto#bvalues(1); insertinto#bvalues(2);

内连连（INNERJOIN）

[sql]view plaincopyselect*from#ainnerjoin#bon#a.id=#b.id

查询结果：

左外连接（LEFT OUTER JOIN）

[sql]view plaincopyselect*from#aLEFTOUTERJOIN#bon#a.id=#b.id 其他OUTER可以省略

查询结果：

[sql]view plaincopyselect*from#bLEFTOUTERJOIN#aon#a.id=#b.id 查询结果：

右外连接（RIGHTOUTER JOIN）

[sql]view plaincopyselect*from#bRIGHTOUTERJOIN#aon#a.id=#b.id 查询结果：[sql]view plaincopyselect*from#aRIGHTOUTERJOIN#bon#a.id=#b.id

全连接（FULLOUTER JOIN）

[sql]view plaincopyselect*from#aFULLJOIN#bon#a.id=#b.id

[sql]view plaincopyselect*from#bFULLJOIN#aon#a.id=#b.id <imgsrc="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）[sql]view plaincopyselect*from#aCROSSJOIN#b