经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 程序设计 » SQL语言 » 查看文章
图解SQL语句里的各种JOIN查询 瞬间秒懂
来源：ITPUB　　作者：潘旭网　　时间：2019/1/30 10:09:33　　对本文有异议

从业以来主要在做客户端，用到的数据库都是表结构比较简单的 SQLite，以我那还给老师一大半的 SQL 水平倒也能对付。现在偶尔需要到后台的 SQL Server 里追查一些数据问题，就显得有点捉襟见肘了，特别是各种 JOIN，有时候傻傻分不清楚，于是索性弄明白并做个记录。

前言

在各种问答社区里谈及 SQL 里的各种 JOIN 之间的区别时，最被广为引用的是 CodeProject 上 C.L. Moffatt 的文章 Visual Representation of SQL Joins，他确实讲得简单明了，使用文氏图来帮助理解，效果明显。本文将沿用他的讲解方式，稍有演绎，可以视为该文较为粗糙的中译版。

约定

下文将使用两个数据库表 Table_A 和 Table_B 来进行示例讲解，其结构与数据分别如下：

  1. mysql> SELECT * FROM Table_A ORDER BY PK ASC;
  2. +----+---------+
  3. | PK | Value |
  4. +----+---------+
  5. | 1 | both ab |
  6. | 2 | only a |
  7. +----+---------+
  8. 2 rows in set (0.00 sec)
  9. mysql> SELECT * from Table_B ORDER BY PK ASC;
  10. +----+---------+
  11. | PK | Value |
  12. +----+---------+
  13. | 1 | both ab |
  14. | 3 | only b |
  15. +----+---------+
  16. 2 rows in set (0.00 sec)

其中 PK 为 1 的记录在 Table_A 和 Table_B 中都有，2 为 Table_A 特有，3 为 Table_B 特有。

常用的 JOIN

INNER JOIN

INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。

文氏图：

1.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. INNER JOIN Table_B B
  5. ON A.PK = B.PK;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | 1 | 1 | both ab | both ab |
  5. +------+------+---------+---------+
  6. 1 row in set (0.00 sec)

注：其中 A 为 Table_A 的别名，B 为 Table_B 的别名，下同。

LEFT JOIN

LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN。左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。

文氏图：

2.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. LEFT JOIN Table_B B
  5. ON A.PK = B.PK;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | 1 | 1 | both ab | both ba |
  5. | 2 | NULL | only a | NULL |
  6. +------+------+---------+---------+
  7. 2 rows in set (0.00 sec)

RIGHT JOIN

RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN。右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。

文氏图：

3.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. RIGHT JOIN Table_B B
  5. ON A.PK = B.PK;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | 1 | 1 | both ab | both ba |
  5. | NULL | 3 | NULL | only b |
  6. +------+------+---------+---------+
  7. 2 rows in set (0.00 sec)

FULL OUTER JOIN

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

文氏图：

4.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. FULL OUTER JOIN Table_B B
  5. ON A.PK = B.PK;

查询结果：

  1. 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
  2. ON A.PK = B.PK' at line 4

注：我当前示例使用的 MySQL 不支持 FULL OUTER JOIN。

应当返回的结果（使用 UNION 模拟）：

  1. mysql> SELECT *
  2.  -> FROM Table_A
  3.  -> LEFT JOIN Table_B
  4.  -> ON Table_A.PK = Table_B.PK
  5.  -> UNION ALL
  6.  -> SELECT *
  7.  -> FROM Table_A
  8.  -> RIGHT JOIN Table_B
  9.  -> ON Table_A.PK = Table_B.PK
  10.  -> WHERE Table_A.PK IS NULL;
  11. +------+---------+------+---------+
  12. | PK | Value | PK | Value |
  13. +------+---------+------+---------+
  14. | 1 | both ab | 1 | both ba |
  15. | 2 | only a | NULL | NULL |
  16. | NULL | NULL | 3 | only b |
  17. +------+---------+------+---------+
  18. 3 rows in set (0.00 sec)

小结

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

5.jpg

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

延伸用法

LEFT JOIN EXCLUDING INNER JOIN

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

文氏图：

6.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. LEFT JOIN Table_B B
  5. ON A.PK = B.PK
  6. WHERE B.PK IS NULL;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | 2 | NULL | only a | NULL |
  5. +------+------+---------+---------+
  6. 1 row in set (0.01 sec)

RIGHT JOIN EXCLUDING INNER JOIN

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

文氏图：

7.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. RIGHT JOIN Table_B B
  5. ON A.PK = B.PK
  6. WHERE A.PK IS NULL;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | NULL | 3 | NULL | only b |
  5. +------+------+---------+---------+
  6. 1 row in set (0.00 sec)

FULL OUTER JOIN EXCLUDING INNER JOIN

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

文氏图：

8.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. FULL OUTER JOIN Table_B B
  5. ON A.PK = B.PK
  6. WHERE A.PK IS NULL
  7. OR B.PK IS NULL;

因为使用到了 FULL OUTER JOIN，MySQL 在执行该查询时再次报错。

  1. 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
  2. ON A.PK = B.PK
  3. WHERE A.PK IS NULL
  4. OR B.PK IS NULL' at line 4

应当返回的结果（用 UNION 模拟）：

  1. mysql> SELECT *
  2.  -> FROM Table_A
  3.  -> LEFT JOIN Table_B
  4.  -> ON Table_A.PK = Table_B.PK
  5.  -> WHERE Table_B.PK IS NULL
  6.  -> UNION ALL
  7.  -> SELECT *
  8.  -> FROM Table_A
  9.  -> RIGHT JOIN Table_B
  10.  -> ON Table_A.PK = Table_B.PK
  11.  -> WHERE Table_A.PK IS NULL;
  12. +------+--------+------+--------+
  13. | PK | Value | PK | Value |
  14. +------+--------+------+--------+
  15. | 2 | only a | NULL | NULL |
  16. | NULL | NULL | 3 | only b |
  17. +------+--------+------+--------+
  18. 2 rows in set (0.00 sec)

总结

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

9.jpg

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

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

10.jpg

更新：更多的 JOIN

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

CROSS JOIN

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

图示：

11.jpg

示例查询：

  1. SELECT A.PK AS A_PK, B.PK AS B_PK,
  2.  A.Value AS A_Value, B.Value AS B_Value
  3. FROM Table_A A
  4. CROSS JOIN Table_B B;

查询结果：

  1. +------+------+---------+---------+
  2. | A_PK | B_PK | A_Value | B_Value |
  3. +------+------+---------+---------+
  4. | 1 | 1 | both ab | both ba |
  5. | 2 | 1 | only a | both ba |
  6. | 1 | 3 | both ab | only b |
  7. | 2 | 3 | only a | only b |
  8. +------+------+---------+---------+
  9. 4 rows in set (0.00 sec)

上面讲过的几种 JOIN 查询的结果都可以用 CROSS JOIN 加条件模拟出来，比如 INNER JOIN 对应 CROSS JOIN ... WHERE A.PK = B.PK。

SELF JOIN

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

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

  1. +--------+----------+-------------+
  2. | EMP_ID | EMP_NAME | EMP_SUPV_ID |
  3. +--------+----------+-------------+
  4. | 1001 | Ma | NULL |
  5. | 1002 | Zhuang | 1001 |
  6. +--------+----------+-------------+
  7. 2 rows in set (0.00 sec)

EMP_ID 字段表示员工 ID，EMP_NAME 字段表示员工姓名，EMP_SUPV_ID 表示主管 ID。

示例查询：

现在我们想查询所有有主管的员工及其对应的主管 ID 和姓名，就可以用 SELF JOIN 来实现。

  1. SELECT A.EMP_ID AS EMP_ID, A.EMP_NAME AS EMP_NAME,
  2.  B.EMP_ID AS EMP_SUPV_ID, B.EMP_NAME AS EMP_SUPV_NAME
  3. FROM Table_C A, Table_C B
  4. WHERE A.EMP_SUPV_ID = B.EMP_ID;

查询结果：

  1. +--------+----------+-------------+---------------+
  2. | EMP_ID | EMP_NAME | EMP_SUPV_ID | EMP_SUPV_NAME |
  3. +--------+----------+-------------+---------------+
  4. | 1002 | Zhuang | 1001 | Ma |
  5. +--------+----------+-------------+---------------+
  6. 1 row in set (0.00 sec)

补充说明

  1. 文中的图使用 Keynote 绘制；

  2. 个人的体会是 SQL 里的 JOIN 查询与数学里的求交集、并集等很像；

  3. SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果；

  4. MySQL 不支持 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果；


 友情链接：直通硅谷 　点职佳 　北美留学生论坛

SQL语言热门文章

  • 为什么不推荐使用 select * ？是性能问题吗 ？
  • sql查询返回值使用map封装多个key和value实例
  • 掌握把“烂”SQL牢牢关进笼子里的密钥
  • 029.[转] SSO单点登录的通用架构实现
  • 千万级用户系统SQL调优实战分享
  • 图解SQL语句里的各种JOIN查询 瞬间秒懂
  • pgsql查询优化之模糊查询
  • 关系型数据库范式 你了解吗？
  • 神奇的 SQL 之谓词 → 难理解的 EXISTS
  • insert和select结合实现"插入某字段在数据库中的最大值＋1"的方法

    • SQL语言推荐文章

  • EF Core性能优化技巧
  • 【SQL】Lag/Rank/Over窗口函数揭秘，数据分析之旅
  • SQL Thinking
  • 【SQL】晨光咖啡馆，过滤聚合的微妙碰撞
  • SHOW PROCESSLIST 最多能显示多长的 SQL？
  • monaco-editor 的 Language Services
  • OceanBase 金融项目优化案例
  • SQL KEEP 窗口函数等价改写案例
  • DM数据库金融行业案例（水贴一波）
  • MindSpore自定义算子中的张量维度问题

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号