MySql 各种join的使用方法

具体详解如下

1 INNER JOIN(内连接)

  

SELECT * FORM

TABLE_A  A  INNER JOIN TABLE_B B ON A.KEY = B.KEY

2 LEFT JOIN (左连接)

 

SELECT * FORM

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