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

心已赠人 2022-03-11 11:54 254阅读 0赞

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

![1240][]

具体分解如下：

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

![1240 1][]

SELECT<select_list>
    
    FROMTable_A A
    
    INNERJOINTable_B B
    
    ONA.Key= B.Key

**2.LEFT JOIN（左连接）**

![1240 2][]

SELECT
    
    FROMTable_A A
    
    LEFTJOINTable_B B
    
    ONA.Key= B.Key

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

![1240 3][]

SELECT
    
    FROMTable_A A
    
    RIGHTJOINTable_B B
    
    ONA.Key= B.Key

**4.OUTER JOIN（外连接）**

![1240 4][]

SELECT
    
    FROMTable_A A
    
    FULLOUTERJOINTable_B B
    
    ONA.Key= B.Key

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

![1240 5][]

SELECT<select_list>
    
    FROMTable_A A
    
    LEFTJOINTable_B B
    
    ONA.Key= B.Key
    
    WHERE
    
    B.KeyISNULL

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

![1240 6][]

SELECT
    
    FROMTable_A A
    
    RIGHTJOINTable_B B
    
    ONA.Key= B.Key
    
    WHERE
    
    A.KeyISNULL

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

![1240 7][]

SELECT
    
    FROMTable_A A
    
    FULLOUTERJOINTable_B B
    
    ONA.Key= B.Key
    
    WHERE
    
    A.KeyISNULLORB.KeyISNULL

***扩展阅读***

[有史以来最牛的一张程序员职业路线图！][Link 1]

[两张图学会VIM编辑器][VIM]

[34张架构史上最全技术知识图谱][34]

[几张趣图助你理解HTTP状态码][HTTP]

[三分钟看懂Nginx服务器的缓存原理和机制][Nginx]

来源：https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

![70][]

[1240]: /images/20220311/fbae30b1e2554d168b7a15c2c7719994.png
[1240 1]: /images/20220311/4d749a68999549859cc5571ecc17d2c6.png
[1240 2]: /images/20220311/7a2c7d2b497a4c69bbcff57485830c5c.png
[1240 3]: /images/20220311/2a162517464c4b67a68f7789fbbc81d2.png
[1240 4]: /images/20220311/a882d622d2164aeaacf5eb3ce7b74604.png
[1240 5]: /images/20220311/897f8fedaa6842a5b7b5e91c83e241be.png
[1240 6]: /images/20220311/28966fb77fdc4dcca8153aae96f8b10a.png
[1240 7]: /images/20220311/a76a209f6b12491dae58ef4d3638d986.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247484682&idx=1&sn=1976c383c192546de0c118a9e0ec833d&chksm=fe7954d2c90eddc4e85dde0ec39d7bfa67a5c9a00f74d2c2ba850d54efcb3f008904e1ce623d&scene=21#wechat_redirect
[VIM]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247485608&idx=3&sn=4060117f010048eaecd28176dd91b378&chksm=fe795970c90ed0662eeefc9a00d0d03cc28273bf385b7d7d04412d12d1974ba952f115e38bd2&scene=21#wechat_redirect
[34]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247486033&idx=3&sn=8b79eae6986cb5af1a60317f7ce55598&chksm=fe795b89c90ed29f3206f7e7a34fa5b3fe7e9d1563ef456269a199a50de03e927af586f6d513&scene=21#wechat_redirect
[HTTP]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247484202&idx=2&sn=25a6f7fcced30156ff7fe342ce7b5a8a&chksm=fe7952f2c90edbe4e19681c1665b17a93a6a81e8ecc97f6a997de814bd883c330189a7fd9efe&scene=21#wechat_redirect
[Nginx]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247485659&idx=1&sn=da1c84634f9e2d43b083a89ebe0af7c4&chksm=fe795903c90ed01557f35077ccf4660fe4102133078239154f40aa49100f1787711fe45121bd&scene=21#wechat_redirect
[70]: /images/20220311/b9d2b69fbcb549239300346f5fd1aed0.png