SQL 的各种 JOIN 用法

绝地灬酷狼 2021-12-09 01:49 428阅读 0赞

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

[![sql-join.png][]][sql-join.png]

### Inner JOIN ###

![1548731841-2663-INNER-JOIN.png][]

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

### Left JOIN ###

![1548731843-2683-LEFT-JOIN.png][]

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

### Right JOIN ###

![1548731845-3187-RIGHT-JOIN.png][]

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

### Outer JOIN ###

![1548731847-7074-FULL-OUTER-JOIN.png][]

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

### Left Excluding JOIN ###

![1548731849-9936-LEFT-EXCLUDING-JOIN.png][]

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

### Right Excluding JOIN ###

![1548731850-7833-RIGHT-EXCLUDING-JOIN.png][]

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

### Outer Excluding JOIN ###

![1548731854-3464-OUTER-EXCLUDING-JOIN.png][]

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

[sql-join.png]: /images/20211209/4a874368db2d48c8953db4882a8acfa4.png
[1548731841-2663-INNER-JOIN.png]: /images/20211209/5209934d73dc400e8106fcb1ec4234ac.png
[1548731843-2683-LEFT-JOIN.png]: /images/20211209/b776e498fa904a7386606535fee1de38.png
[1548731845-3187-RIGHT-JOIN.png]: /images/20211209/1de77401c53f491b93cc785afe5f4912.png
[1548731847-7074-FULL-OUTER-JOIN.png]: /images/20211209/e715c971a6f6482d859abf557512414c.png
[1548731849-9936-LEFT-EXCLUDING-JOIN.png]: /images/20211209/938186667b5647399e4dcc237bdcade4.png
[1548731850-7833-RIGHT-EXCLUDING-JOIN.png]: /images/20211209/1905487db50145b9a6e54193ce6b866f.png
[1548731854-3464-OUTER-EXCLUDING-JOIN.png]: /images/20211209/70913445d0884295bab77d9df2b78874.png