一张图看懂 SQL 的各种 join 用法

骑猪看日落 2021-08-01 00:20 515阅读 0赞

![640][]

作者 | C.L. Moffatt

来源 | codeproject

**导读：**下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。  
  
**www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins**

![640?wx\_fmt=png][640_wx_fmt_png]

具体分解如下：

**`1.INNER JOIN（内连接）`**

![640?wx\_fmt=png][640_wx_fmt_png 1]

**``**

`SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key`

`2.LEFT JOIN（左连接）`

SELECT *<select\_list>*  
FROM Table\_A A  
LEFT JOIN Table\_B B  
ON A.Key = B.Key

**``**

**`3.RIGHT JOIN（右连接）`**

![640?wx\_fmt=png][640_wx_fmt_png 2]

**``**

`SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key`

`4.OUTER JOIN（外连接）`

SELECT *<select\_list>*  
FROM Table\_A A  
FULL OUTER JOIN Table\_B B  
ON A.Key = B.Key

**``**

**`5.LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）`**

![640?wx\_fmt=png][640_wx_fmt_png 3]

**``**

`SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL`

`6.RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）`

SELECT *<select\_list>*  
FROM Table\_A A  
RIGHT JOIN Table\_B B  
ON A.Key = B.Key  
WHERE A.Key IS NULL

**``**

**`7.OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）`**

![640?wx\_fmt=png][640_wx_fmt_png 4]

**``**

SELECT *<select\_list>*  
FROM Table\_A A  
FULL OUTER JOIN Table\_B B  
ON A.Key = B.Key  
WHERE A.Key IS NULL OR B.Key IS NULL

**↓ ↓ ↓ 加入技术交流群 ↓ ↓ ↓**

![640?wx\_fmt=png][640_wx_fmt_png 5]**推****荐****阅****读**

***1.***

***2. ***

***3.***

***4.***

![640?][640 1]

[640]: /images/20210731/3e1cdbd19fa94bd280be86143fc5f952.png
[640_wx_fmt_png]: /images/20210731/e93a7337c76c437291350db05c032a78.png?wx_fmt=png
[640_wx_fmt_png 1]: /images/20210731/9a3549cda0f54f65a56973c6464e7adb.png?wx_fmt=png
[640_wx_fmt_png 2]: /images/20210731/0c3330da7fbf4fe98a8272f03d3eebcc.png?wx_fmt=png
[640_wx_fmt_png 3]: /images/20210731/5ca589222f9445489f9748d67d99614f.png?wx_fmt=png
[640_wx_fmt_png 4]: /images/20210731/1500321fbefb496bb0f073964d6948cb.png?wx_fmt=png
[640_wx_fmt_png 5]: /images/20210731/9b9ac45096124861966fe2b11ff87492.png?wx_fmt=png
[640 1]: /images/20210731/b7acb3c4b1a54c56b984b808b7369a27.png?