SQL 的各种 join 用法
参考网址:https://mp.weixin.qq.com/s?__biz=MzA5ODUzOTA0OQ==&mid=2651690076&idx=2&sn=9d52842e835e15fff9c8219e95cdfbe1&chksm=8b69371abc1ebe0cb8a5196d1683a5967713ecc9c07b400a87a766d9a9c893eb5183a52f8b17&mpshare=1&scene=1&srcid=04015dgLPVCk9semPZxymWqO&pass_ticket=VJ4Mb7w6Vnn0kYUaVUrGfKLrV01D9QUQseGbhxeleZiKGpNqqVqPFMKWz8DIu9Sp#rd
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
1、INNER JOIN(内连接)
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(右连接)
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
4、OUTER JOIN(外连接)(mysql数据库不支持 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(左连接-内连接)
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(外连接-内连接)
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