SQL各种join用法

来自微信公众号“Java编程精选”

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

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.RIGTH JOIN 右连接

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 左连接-内连接

SELECT <slelct_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