图解SQL里的各种 JOIN，看完不懂来找我！（二）

4、FULL OUTER JOIN

FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

文氏图：

FULL OUTER 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
FULLOUTERJOIN 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 inset (0.00 sec)


小结

以上四种，就是 SQL 里常见 JOIN 的种类和概念了，看一下它们的合影：

有没有感觉少了些什么，学数学集合时完全不止这几种情况？确实如此，继续看。

延伸用法

1、LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表没有关联数据的记录集。

文氏图：

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
LEFTJOIN Table_B B
ON A.PK = B.PK
WHERE B.PK ISNULL;
查询结果：

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    2 | NULL | only a  | NULL    |
+------+------+---------+---------+
1 row inset (0.01 sec)


2、RIGHT JOIN EXCLUDING INNER JOIN

返回右表有但左表没有关联数据的记录集。

文氏图：

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
RIGHTJOIN Table_B B
ON A.PK = B.PK
WHERE A.PK ISNULL;


查询结果：

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| NULL |    3 | NULL    | only b  |
+------+------+---------+---------+
1 row inset (0.00 sec)