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

3、FULL OUTER JOIN EXCLUDING INNER JOIN

返回左表和右表里没有相互关联的记录集。

文氏图：

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


因为使用到了 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 inset (0.00 sec)

总结

以上七种用法基本上可以覆盖各种 JOIN 查询了。七种用法的全家福：

看着它们，我仿佛回到了当年学数学，求交集并集的时代……顺带张贴一下 C.L. Moffatt 带 SQL 语句的图片，配合学习，风味更佳：

更多的 JOIN

除以上几种外，还有更多的 JOIN 用法，比如 CROSS JOIN（迪卡尔集）、SELF JOIN，可以参考 SQL JOINS Slide Presentation 学习。

1、CROSS 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
CROSSJOIN Table_B B;


查询结果：

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
|    2 |    1 | only a  | both ba |
|    1 |    3 | both ab | only b  |
|    2 |    3 | only a  | only b  |
+------+------+---------+---------+
4 rows inset (0.00 sec)
上面讲过的几种 JOIN 查询的结果都可以用 CROSS JOIN 加条件模拟出来，比如 INNER JOIN 对应 CROSS JOIN ... WHERE A.PK = B.PK。

2、SELF JOIN

返回表与自己连接后符合条件的记录，一般用在表里有一个字段是用主键作为外键的情况。比如 Table_C 的结构与数据如下：

+--------+----------+-------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID |
+--------+----------+-------------+
|   1001 | Ma       |        NULL |
|   1002 | Zhuang   |        1001 |
+--------+----------+-------------+
2 rows inset (0.00 sec)
EMP_ID 字段表示员工 ID，EMP_NAME 字段表示员工姓名，EMP_SUPV_ID 表示主管 ID。

示例查询：

现在我们想查询所有有主管的员工及其对应的主管 ID 和姓名，就可以用 SELF JOIN 来实现。SELECT A.EMP_ID AS EMP_ID, A.EMP_NAME AS EMP_NAME,
   B.EMP_ID AS EMP_SUPV_ID, B.EMP_NAME AS EMP_SUPV_NAME
FROM Table_C A, Table_C B
WHERE A.EMP_SUPV_ID = B.EMP_ID;
查询结果：+--------+----------+-------------+---------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID | EMP_SUPV_NAME |
+--------+----------+-------------+---------------+
|   1002 | Zhuang   |        1001 | Ma            |
+--------+----------+-------------+---------------+
1 row inset (0.00 sec)