图解MySQL里的各种 JOIN,看完不懂来找我!

数据库 MySQL
从业以来主要在做客户端,用到的数据库都是表结构比较简单的 SQLite,以我那还给老师一大半的 SQL 水平倒也能对付。

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

 

[[274993]]

前言

在各种问答社区里谈及 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. rows in set (0.00 sec) 
  9.  
  10. mysql> SELECT * from Table_B ORDER BY PK ASC
  11. +----+---------+ 
  12. | PK | Value   | 
  13. +----+---------+ 
  14. |  1 | both ab | 
  15. |  3 | only b  | 
  16. +----+---------+ 
  17. rows in set (0.00 sec) 

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

常用的 JOIN

1、INNER JOIN

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

文氏图:

INNER JOIN

 

示例查询:

  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 的别名,下同。

2、LEFT JOIN

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

文氏图:

 


LEFT JOIN

 

 

示例查询:

  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. rows in set (0.00 sec) 

3、RIGHT JOIN

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

文氏图:

 


RIGHT JOIN

 

 

示例查询:

  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. rows in set (0.00 sec) 

4、FULL OUTER JOIN

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

文氏图:

 


FULL OUTER JOIN

 

 

示例查询:

  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. rows in set (0.00 sec) 

小结

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

小结

 

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

延伸用法

1、LEFT JOIN EXCLUDING INNER JOIN

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

文氏图:

 


LEFT JOIN EXCLUDING INNER JOIN

 

 

示例查询:

  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) 

2、RIGHT JOIN EXCLUDING INNER JOIN

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

文氏图:


RIGHT JOIN EXCLUDING INNER JOIN

 

 

 

示例查询:

  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) 

3、FULL OUTER JOIN EXCLUDING INNER JOIN

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

文氏图:

 

 


FULL OUTER JOIN EXCLUDING INNER JOIN

 

 

 

示例查询:

  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 NULLat 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. rows in set (0.00 sec) 

总结

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

 

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

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

 

更多的 JOIN

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

1、CROSS JOIN

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

图示:

 

示例查询:

  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. rows in set (0.00 sec) 

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

2、SELF JOIN

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

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

  1. +--------+----------+-------------+ 
  2. | EMP_ID | EMP_NAME | EMP_SUPV_ID | 
  3. +--------+----------+-------------+ 
  4. |   1001 | Ma       |        NULL | 
  5. |   1002 | Zhuang   |        1001 | 
  6. +--------+----------+-------------+ 
  7. 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) 

 

责任编辑:武晓燕 来源: mazhuang
相关推荐

2020-10-09 09:49:18

HTTPS网络 HTTP

2019-11-13 10:31:49

Kafka架构高可用

2021-05-10 08:34:37

USB接口USB网络设备

2022-02-22 08:25:51

typeScript泛型概念泛型使用

2019-04-16 15:18:28

SQLJOIN数据库

2021-05-28 11:54:29

MySQL数据库主从复制

2010-05-21 17:30:28

2021-09-06 07:58:47

链表数据结构

2020-06-18 10:48:44

Linux 系统 数据

2019-03-18 15:00:48

SQLJoin用法数据库

2022-03-27 09:06:25

vuexActionsMutations

2018-02-25 22:37:34

2023-12-01 08:39:29

分布式锁系统

2021-08-11 21:46:47

MySQL索引join

2018-04-03 15:20:07

数据库MySQLjoin连接

2020-11-04 08:37:37

C语言C++内存

2010-01-21 17:15:22

可网管交换机

2020-05-08 10:48:49

forkjoinJava

2021-12-06 10:22:47

切片拷贝Python

2023-08-09 09:03:49

CPU密集型运算
点赞
收藏

51CTO技术栈公众号