这是我参与8月更文挑战的第19天,活动详情查看:8月更文挑战
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 JOIN)
FULL JOIN 一般被译作外连接、全连接,实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录,其中左右表里能关联起来的记录被连接后返回。(MySQL不支持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
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常见缩影
5 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)
6 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)
7 FULL 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)
SQL所有JOIN
8 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
CROSS JOIN 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 in set (0.00 sec)
上面讲过的几种 JOIN 查询的结果都可以用 CROSS JOIN 加条件模拟出来,比如 INNER JOIN 对应 CROSS JOIN ... WHERE A.PK = B.PK。
9 SELF JOIN
返回表与自己连接后符合条件的记录,一般用在表里有一个字段是用主键作为外键的情况。比如 Table_C 的结构与数据如下:
+--------+----------+-------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID |
+--------+----------+-------------+
| 1001 | Ma | NULL |
| 1002 | Zhuang | 1001 |
+--------+----------+-------------+
2 rows in set (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 in set (0.00 sec)
补充说明
- 文中的图使用 Keynote 绘制
- 个人的体会是 SQL 里的 JOIN 查询与数学里的求交集、并集等很像;SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 来达到相同的效果
- MySQL 不支持 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 来达到相同的效果