MySql 各种join的使用方法

转载自榆阴下的一潭

具体详解如下
1 INNER JOIN(内连接)

SELECT FROM
TABLE_A A INNER JOIN TABLE_B B ON A.KEY = B.KEY
2 LEFT JOIN (左连接)


SELECT FROM
TABLE_A A LEFT JOIN TABLE_B B ON A.KEY = B.KEY
3 RIGHT JOIN (右连接)

SELECT FROM
TABLE_A A RIGHT TABLE_B B ON A.KEY = B.KEY
4.OUTER JOIN (外连接)


SELECT FROM
TABLE_A A FULL
OUTER JOIN TABLE_B B ON A.KEY = B.KEY

5 LEFT JOIN EXCLUDING INNER JOIN (左连接 - 内连接)

SELECT FROM
TABLE_A A
LEFT JOIN TABLE_B ON A.KEY = B.KEY WHERE B.KEY IS NULL

6 RIGHT JOIN EXCLUDING INNER JOIN (右连接 - 内连接)

SELECT 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 * FROM
TABLE_A
FULL OUTER JOIN TABLE_B B ON A.KEY = B.KEY WHERE A.KEY IS NULL OR B.KEY IS NULL