常见通用的join查询

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、join的五种连接：

内连接(INNER JOIN)：

显示两个表或多个表中共有的数据，只有在两个表中匹配的行才能在结果集中出现。

左向外连接(LEFT JOIN/LEFT OUTER JOIN)：

简称左连接，即以左表为基表，显示左表所有的数据，若左表中行的数据在右表中没有相应的匹配行的话，则结果表中右表中的列返回null值。

右向外连接(RIGHT JOIN/RIGHT OUTER JOIN)：

简称右连接，即以右表为基表，显示右表所有的数据，若右表中行的数据在左表中没有相应的匹配行的话，则结果表中左表中的列返回null值。

完整外连接(FULL JOIN)：

简称全连接，即将左表和右表的所有数据全都显示，当某行在另一张表中没有匹配行，则另一表中的列返回null。

交叉连接(笛卡尔积)(CROSS JOIN):

(1)、CROSS JOIN(笛卡尔积)是两个表每一个字段相互匹配， 得出的结果就是笛卡尔积。笛卡尔积也等同于交叉连接。
(2)、CROSS JOIN(笛卡尔积)带条件查询， 查询结果跟等值连接(内连接)(INNER JOIN连接)、where连接的查询结果是一样，并且后面加条件只能用where,不能用on。

二、七种Join理论

1.常用Join韦恩图

# 1
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

# 2
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

# 3
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

# 4 
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

# 5
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

# 6 
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;

# 7 
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;

2.mysql的Join韦恩图

# 1
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

# 2
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

# 3
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

# 4 
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

# 5
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

# 6 
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

# 7 
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;