mysql join 不等于_图解MySQL里的各种 JOIN，看完不懂来找我！

最新推荐文章于 2022-05-29 17:58:31 发布

小timtim

最新推荐文章于 2022-05-29 17:58:31 发布

阅读量908

点赞数

文章标签： mysql join 不等于

本文链接：https://blog.csdn.net/weixin_34115768/article/details/114328434

版权

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

前言

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

约定

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

mysql>SELECT*FROMTable_AORDERBYPKASC;

+----+---------+

| PK | Value |

+----+---------+

| 1 | both ab |

| 2 | onlya |

+----+---------+

2 rowsinset(0.00 sec)

mysql> SELECT*fromTable_BORDERBYPKASC;

+----+---------+

| PK | Value |

+----+---------+

| 1 | both ab |

| 3 | onlyb |

+----+---------+

2 rowsinset(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

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

INNERJOINTable_B B

ONA.PK = B.PK;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

| 1 | 1 | both ab | both ab |

+------+------+---------+---------+

1 row inset(0.00 sec)

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

2、LEFT JOIN

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

文氏图：

LEFT JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

LEFTJOINTable_B B

ONA.PK = B.PK;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

| 1 | 1 | both ab | both ba |

+------+------+---------+---------+

2 rowsinset(0.00 sec)

3、RIGHT JOIN

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

文氏图：

RIGHT JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

RIGHTJOINTable_B B

ONA.PK = B.PK;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

| 1 | 1 | both ab | both ba |

+------+------+---------+---------+

2 rowsinset(0.00 sec)

4、FULL OUTER JOIN

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

文氏图：

FULL OUTER JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

FULLOUTERJOINTable_B B

ONA.PK = B.PK;

查询结果：

ERROR 1064 (42000): You have an errorinyour SQL syntax;checkthe manual that correspondstoyour MySQL server versionfortherightsyntaxtouse near 'FULLOUTERJOINTable_B B

ONA.PK = B.PK'atline 4

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

应当返回的结果(使用 UNION 模拟)：

mysql>SELECT*

-> FROMTable_A

-> LEFTJOINTable_B

-> ONTable_A.PK = Table_B.PK

-> UNIONALL

-> SELECT*

-> FROMTable_A

-> RIGHTJOINTable_B

-> ONTable_A.PK = Table_B.PK

-> WHERETable_A.PKISNULL;

+------+---------+------+---------+

| PK | Value | PK | Value |

+------+---------+------+---------+

| 1 | both ab | 1 | both ba |

+------+---------+------+---------+

3 rowsinset(0.00 sec)

小结

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

小结

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

延伸用法

1、LEFT JOIN EXCLUDING INNER JOIN

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

文氏图：

LEFT JOIN EXCLUDING INNER JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

LEFTJOINTable_B B

ONA.PK = B.PK

WHEREB.PKISNULL;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

+------+------+---------+---------+

1 row inset(0.01 sec)

2、RIGHT JOIN EXCLUDING INNER JOIN

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

文氏图：

RIGHT JOIN EXCLUDING INNER JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

RIGHTJOINTable_B B

ONA.PK = B.PK

WHEREA.PKISNULL;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

+------+------+---------+---------+

1 row inset(0.00 sec)

3、FULL OUTER JOIN EXCLUDING INNER JOIN

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

文氏图：

FULL OUTER JOIN EXCLUDING INNER JOIN

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

FULLOUTERJOINTable_B B

ONA.PK = B.PK

WHEREA.PKISNULL

ORB.PKISNULL;

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

ERROR 1064 (42000): You have an errorinyour SQL syntax;checkthe manual that correspondstoyour MySQL server versionfortherightsyntaxtouse near 'FULLOUTERJOINTable_B B

ONA.PK = B.PK

WHEREA.PKISNULL

ORB.PKISNULL'atline 4

应当返回的结果(用 UNION 模拟)：

mysql>SELECT*

-> FROMTable_A

-> LEFTJOINTable_B

-> ONTable_A.PK = Table_B.PK

-> WHERETable_B.PKISNULL

-> UNIONALL

-> SELECT*

-> FROMTable_A

-> RIGHTJOINTable_B

-> ONTable_A.PK = Table_B.PK

-> WHERETable_A.PKISNULL;

+------+--------+------+--------+

| PK | Value | PK | Value |

+------+--------+------+--------+

+------+--------+------+--------+

2 rowsinset(0.00 sec)

总结

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

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

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

更多的 JOIN

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

1、CROSS JOIN

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

图示：

示例查询：

SELECTA.PKASA_PK, B.PKASB_PK,

A.Value ASA_Value, B.ValueASB_Value

FROMTable_A A

CROSSJOINTable_B B;

查询结果：

+------+------+---------+---------+

| A_PK | B_PK | A_Value | B_Value |

+------+------+---------+---------+

| 1 | 1 | both ab | both ba |

| 2 | 1 | onlya | both ba |

| 1 | 3 | both ab | onlyb |

| 2 | 3 | onlya |onlyb |

+------+------+---------+---------+

4 rowsinset(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 rowsinset(0.00 sec)

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

示例查询：

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

SELECTA.EMP_IDASEMP_ID, A.EMP_NAMEASEMP_NAME,

B.EMP_ID ASEMP_SUPV_ID, B.EMP_NAMEASEMP_SUPV_NAME

FROMTable_C A, Table_C B

WHEREA.EMP_SUPV_ID = B.EMP_ID;

查询结果：

+--------+----------+-------------+---------------+

+--------+----------+-------------+---------------+

| 1002 | Zhuang | 1001 | Ma |

+--------+----------+-------------+---------------+

1 row inset(0.00 sec)

【编辑推荐】

【责任编辑：武晓燕 TEL：(010)68476606】

小timtim

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql join 不等于_图解MySQL里的各种 JOIN，看完不懂来找我！

从业以来主要在做客户端，用到的数据库都是表结构比较简单的 SQLite，以我那还给老师一大半的 SQL 水平倒也能对付。现在偶尔需要到后台的 SQL Server 里追查一些数据问题，就显得有点捉襟见肘了，特别是各种 JOIN，有时候傻傻分不清楚，于是索性弄明白并做个记录。前言在各种问答社区里谈及 SQL 里的各种 JOIN 之间的区别时，最被广为引用的是 CodeProject 上 C.L. ...
复制链接

扫一扫