本文转载至https://mazhuang.org/2017/09/11/joins-in-sql/#full-outer-join-excluding-inner-join，如需阅读原文请至上述链接去往，原作者有更多关于数据库相关文章，写的不错，推荐一下！
下文将使用两个数据库表 Table_A 和 Table_B 来进行示例讲解，其结构与数据分别如下：
mysql> SELECT * FROM Table_A ORDER BY PK ASC;
+----+---------+
| PK | Value |
+----+---------+
| 1 | both ab |
| 2 | only a |
+----+---------+
2 rows in set (0.00 sec)
mysql> SELECT * from Table_B ORDER BY PK ASC;
+----+---------+
| PK | Value |
+----+---------+
| 1 | both ab |
| 3 | only b |
+----+---------+
2 rows in set (0.00 sec)
其中 PK 为 1 的记录在 Table_A 和 Table_B 中都有，2 为 Table_A 特有，3 为 Table_B 特有。
常用的 JOIN：
1、INNER JOIN
INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;
查询结果：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| 1 | 1 | both ab | both ab |
+------+------+---------+---------+
1 row in set (0.00 sec)
注：其中 A 为 Table_A 的别名，B 为 Table_B 的别名，下同。
2、LEFT JOIN
LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN。左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK;
查询结果：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| 1 | 1 | both ab | both ba |
| 2 | NULL | only a | NULL |
+------+------+---------+---------+
2 rows in set (0.00 sec)
3、RIGHT JOIN
RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN。右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK;
查询结果：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| 1 | 1 | both ab | both ba |
| NULL | 3 | NULL | only b |
+------+------+---------+---------+
2 rows in set (0.00 sec)
4、FULL OUTER JOIN
FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK;
查询结果：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK' at line 4
注：我当前示例使用的 MySQL 不支持 FULL OUTER JOIN。
应当返回的结果（使用 UNION 模拟）：
mysql> SELECT *
-> FROM Table_A
-> LEFT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> UNION ALL
-> SELECT *
-> FROM Table_A
-> RIGHT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_A.PK IS NULL;
+------+---------+------+---------+
| PK | Value | PK | Value |
+------+---------+------+---------+
| 1 | both ab | 1 | both ba |
| 2 | only a | NULL | NULL |
| NULL | NULL | 3 | only b |
+------+---------+------+---------+
3 rows in set (0.00 sec)
小结
以上四种，就是 SQL 里常见 JOIN 的种类和概念了，看一下它们的合影：
延伸用法
1、LEFT JOIN EXCLUDING INNER JOIN
返回左表有但右表没有关联数据的记录集。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL;
查询结果：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| 2 | NULL | only a | NULL |
+------+------+---------+---------+
1 row in set (0.01 sec)
2、RIGHT JOIN EXCLUDING INNER JOIN
返回右表有但左表没有关联数据的记录集。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL;
查询结果：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| NULL | 3 | NULL | only b |
+------+------+---------+---------+
1 row in set (0.00 sec)
3、FULL OUTER JOIN EXCLUDING INNER JOIN
返回左表和右表里没有相互关联的记录集。
文氏图：
示例查询：
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL;
因为使用到了 FULL OUTER JOIN，MySQL 在执行该查询时再次报错。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL' at line 4
应当返回的结果（用 UNION 模拟）：
mysql> SELECT *
-> FROM Table_A
-> LEFT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_B.PK IS NULL
-> UNION ALL
-> SELECT *
-> FROM Table_A
-> RIGHT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_A.PK IS NULL;
+------+--------+------+--------+
| PK | Value | PK | Value |
+------+--------+------+--------+
| 2 | only a | NULL | NULL |
| NULL | NULL | 3 | only b |
+------+--------+------+--------+
2 rows in set (0.00 sec)
总结
以上七种用法基本上可以覆盖各种 JOIN 查询了。七种用法的全家福：
补充说明
文中的图使用 Keynote 绘制；
个人的体会是 SQL 里的 JOIN 查询与数学里的求交集、并集等很像；
SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果；
MySQL 不支持 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果；