MySQL高级——MySQL七种join举例

一、左外连接

SELECT * FROM A LEFT JOIN B ON A.KEY = B.KEY

二、右外连接

SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY

三、内连接

SELECT * FROM A INNER JOIN B ON A.KEY = B.KEY

四、左连接

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

五、右连接

SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL

六、全连接

• Mysql不支持下列方式

SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY 

• Mysql支持下列方式

　SELECT * FROM A LEFT JOIN B ON A.KEY=B.KEY
　UNION
　SELECT * FROM A RIGHT JOIN B ON A.KEY=B.KEY

七、全外连接

• mysql不支持下列方式

SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL OR B.KEY IS NULL

• mysql支持下列方式

　SELECT * FROM A LEFT JOIN B ON A.KEY=B.KEY WHERE B.KEY IS NULL
　UNION
　SELECT * FROM A RIGHT JOIN B ON A.KEY=B.KEY WHERE A.KEY IS NULL