phpjoins技巧_图解 SQL 里的各类 JOIN

文章目录 [+]

约定

下文将利用两个数据库表 TableA 和 TableB 来进行示例讲解，其构造与数据分别如下：

phpjoins技巧_图解 SQL 里的各类 JOIN

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 的记录在 TableA 和 TableB 中都有，2 为 TableA 特有，3 为 TableB 特有。

（图片来自网络侵删）

常用的 JOIN

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_ValueFROM Table_A AINNER JOIN Table_B BON 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 的别名，下同。

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_ValueFROM Table_A ALEFT JOIN Table_B BON 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)

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_ValueFROM Table_A ARIGHT JOIN Table_B BON 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)

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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON 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 BON 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 的种类和观点了，看一下它们的合影：

有没有觉得少了些什么，学数学凑集时完备不止这几种情形？确实如此，连续看。

延伸用法

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_ValueFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKWHERE B.PK IS NULL;

查询结果：

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

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_ValueFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULL;

查询结果：

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

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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLOR B.PK IS NULL;

由于利用到了 FULL OUTER JOIN，MySQL 在实行该查询时再次报错。

应该返回的结果（用 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 查询了, 七种用法的百口福：

看着它们，我仿佛回到了当年学数学，求交集并集的时期……

顺带张贴一下 C.L. Moffatt 带 SQL 语句的图片，合营学习，风味更佳：

更多的 JOIN

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

CROSS JOIN

返回左表与右表之间符合条件的记录的迪卡尔集。

图示：

示例查询：

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_ValueFROM Table_A ACROSS 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。

SELF JOIN

返回表与自己连接后符合条件的记录，一样平常用在表里有一个字段是用主键作为外键的情形。

比如 Table_C 的构造与数据如下：

+--------+----------+-------------+| EMP_ID | EMP_NAME | EMP_SUPV_ID |+--------+----------+-------------+| 1001 | Ma | NULL || 1002 | Zhuang | 1001 |+--------+----------+-------------+2 rows in set (0.00 sec)

EMPID 字段表示员工 ID，EMPNAME 字段表示员工姓名，EMPSUPVID 表示主管 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_NAMEFROM Table_C A, Table_C BWHERE 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 来达到相同的效果；

如果你对我的文章感兴趣，可以关注我的微信公众号 isprogrammer 随时阅读更多内容。

参考

[1] Visual Representation of SQL Joins: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

[2] How to do a FULL OUTER JOIN in MySQL?: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql

[3] SQL JOINS Slide Presentation: https://www.w3resource.com/slides/sql-joins-slide-presentation.php

[4] SQL Self Join: https://www.w3resource.com/sql/joins/perform-a-self-join.php