Mysql：ON 与 WHERE 的区别

最新推荐文章于 2023-03-09 10:40:12 发布
最新推荐文章于 2023-03-09 10:40:12 发布
阅读量282 收藏
点赞数
分类专栏： mysql
原文链接：https://www.jianshu.com/p/d923cf8ae25f
版权

即使你认为自己已对 MySQL 的 LEFT JOIN 理解深刻，但我敢打赌，这篇文章肯定能让你学会点东西！

  • ON 子句与 WHERE 子句的不同
  • 一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法 
  • Matching-Conditions 与 Where-conditions 的不同

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个 LFET JOIN 示例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

mysql> CREATETABLE`product` (

`id` int(10) unsigned NOTNULLauto_increment,

`amount` int(10) unsigned defaultNULL,

PRIMARYKEY(`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULTCHARSET=latin1

mysql> CREATETABLE`product_details` (

`id` int(10) unsigned NOTNULL,

`weight` int(10) unsigned defaultNULL,

`exist` int(10) unsigned defaultNULL,

PRIMARYKEY(`id`)

) ENGINE=MyISAM DEFAULTCHARSET=latin1

mysql> INSERTINTOproduct (id,amount)

VALUES(1,100),(2,200),(3,300),(4,400);

Query OK, 4 rowsaffected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> INSERTINTOproduct_details (id,weight,exist)

VALUES(2,22,0),(4,44,1),(5,55,0),(6,66,1);

Query OK, 4 rowsaffected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT* FROMproduct;

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

| id | amount |

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

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

| 4 | 400 |

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

4 rowsinset(0.00 sec)

mysql> SELECT* FROMproduct_details;

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

| id | weight | exist |

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

| 2 | 22 | 0 |

| 4 | 44 | 1 |

| 5 | 55 | 0 |

| 6 | 66 | 1 |

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

4 rowsinset(0.00 sec)

mysql> SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id);

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | 2 | 22 | 0 |

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | 4 | 44 | 1 |

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

4 rowsinset(0.00 sec)

ON 子句和 WHERE 子句有什么不同？

一个问题：下面两个查询的结果集有什么不同么？

1

2

3

4

5

6

1. SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id)

ANDproduct_details.id=2;

2. SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id)

WHEREproduct_details.id=2;

用例子来理解最好不过了：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

mysql> SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id)

ANDproduct_details.id=2;

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | 2 | 22 | 0 |

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | NULL| NULL| NULL|

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

4 rowsinset(0.00 sec)

mysql> SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id)

WHEREproduct_details.id=2;

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

| id | amount | id | weight | exist |

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

| 2 | 200 | 2 | 22 | 0 |

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

1 row inset(0.01 sec)

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。

第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例：

1

2

3

4

5

6

7

8

9

10

11

12

13

mysql>

mysql> SELECT* FROMproduct LEFTJOINproduct_details

ONproduct.id = product_details.id

ANDproduct.amount=100;

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | NULL| NULL| NULL|

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | NULL| NULL| NULL|

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

4 rowsinset(0.00 sec)

所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据）

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECT* FROMproduct LEFTJOINproduct_details

ON(product.id = product_details.id)

ANDproduct.amount=200;

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | 2 | 22 | 0 |

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | NULL| NULL| NULL|

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

4 rowsinset(0.01 sec)

同样，所有来自product表的数据行都被检索到了，有一条数据匹配到了。

使用 WHERE ... IS NULL 子句的 LEFT JOIN

当你使用 WHERE ... IS NULL 子句时会发生什么呢？

如前所述，WHERE 条件查询发生在 匹配阶段之后，这意味着 WHERE ... IS NULL 子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。

纸面上看起来很清楚，但是当你在 ON 子句中使用多个条件时就会感到困惑了。

我总结了一种简单的方式来理解上述情况：

  • 将 IS NULL 作为否定匹配条件
  • 使用 !(A and B) == !A OR !B 逻辑判断

看看下面的示例：

1

2

3

4

5

6

7

8

9

10

11

mysql> SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id ANDb.weight!=44 ANDb.exist=0

WHEREb.id ISNULL;

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

| id | amount |

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

| 1 | 100 |

| 3 | 300 |

| 4 | 400 |

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

3 rowsinset(0.00 sec)

让我们检查一下 ON 匹配子句：

1

(a.id=b.id) AND(b.weight!=44) AND(b.exist=0)

我们可以把 IS NULL 子句 看作是否定匹配条件。

这意味着我们将检索到以下行：

1

2

3

!( exist(b.id that equals toa.id) ANDb.weight !=44 ANDb.exist=0 )

!exist(b.id that equals toa.id) || !(b.weight !=44) || !(b.exist=0)

!exist(b.id that equals toa.id) || b.weight =44 || b.exist=1

就像在C语言中的逻辑 AND 和 逻辑 OR表达式一样，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）

看看别的示例：

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id ANDb.weight!=44 ANDb.exist=1

WHEREb.id ISNULL;

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

| id | amount |

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

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

| 4 | 400 |

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

4 rowsinset(0.00 sec)

Matching-Conditions 与 Where-conditions 之战

如果你吧基本的查询条件放在 ON 子句中，把剩下的否定条件放在 WHERE 子句中，那么你会获得相同的结果。

例如，你可以不这样写：

1

2

3

SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id ANDb.weight!=44 ANDb.exist=0

WHEREb.id ISNULL;

你可以这样写：

1

2

3

SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id

WHEREb.id isnullORb.weight=44 ORb.exist=1;

1

2

3

4

5

6

7

8

9

10

11

mysql> SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id

WHEREb.id isnullORb.weight=44 ORb.exist=1;

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

| id | amount |

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

| 1 | 100 |

| 3 | 300 |

| 4 | 400 |

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

3 rowsinset(0.00 sec)

你可以不这样写：

1

2

3

SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id ANDb.weight!=44 ANDb.exist!=0

WHEREb.id ISNULL;

可以这样写：

1

2

3

SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id

WHEREb.id isnullORb.weight=44 ORb.exist=0;

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECTa.* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id

WHEREb.id isnullORb.weight=44 ORb.exist=0;

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

| id | amount |

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

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

| 4 | 400 |

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

4 rowsinset(0.00 sec)

这些查询真的效果一样？

如果你只需要第一个表中的数据的话，这些查询会返回相同的结果集。有一种情况就是，如果你从 LEFT JOIN的表中检索数据时，查询的结果就不同了。

如前所属，WHERE 子句是在匹配阶段之后用来过滤的。

例如：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

mysql> SELECT* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id ANDb.weight!=44 ANDb.exist=1

WHEREb.id isnull;

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | NULL| NULL| NULL|

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | NULL| NULL| NULL|

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

4 rowsinset(0.00 sec)

mysql> SELECT* FROMproduct a LEFTJOINproduct_details b

ONa.id=b.id

WHEREb.id ISNULLORb.weight=44 ORb.exist=0;

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

| id | amount | id | weight | exist |

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

| 1 | 100 | NULL| NULL| NULL|

| 2 | 200 | 2 | 22 | 0 |

| 3 | 300 | NULL| NULL| NULL|

| 4 | 400 | 4 | 44 | 1 |

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

4 rowsinset(0.00 sec)

总附注：

如果你使用 LEFT JOIN 来寻找在一些表中不存在的记录，你需要做下面的测试：WHERE 部分的 col_name IS NULL(其中 col_name 列被定义为 NOT NULL)，MYSQL 在查询到一条匹配 LEFT JOIN 条件后将停止搜索更多行（在一个特定的组合键下）。

From：https://www.jianshu.com/p/d923cf8ae25f

后知后觉的肖邦
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL查询条件中放置on和where的区别分析
09-09
在MySQL查询中，`ON` 和 `WHERE` 都是用来设置查询条件的，但它们在不同的语句结构中起着不同的作用。理解这两者之间的差异对于编写高效的SQL查询至关重要。 首先，`ON` 关键字主要用于`JOIN`操作，如`INNER JOIN`,...
解析mysql left( right ) join使用on与where筛选的差异
10-27
本篇文章是对mysql left( right )join使用on与where筛选的差异进行了详细的分析介绍，需要的朋友参考下
sql：mysql：on和where区别，on后可跟多个条件
热门推荐
不花的花和尚的博客
08-16 1万+
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下： 1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为...
mysql on和where的区别
爱偷懒的河马先生的博客
08-01 275
on和where的区别
MySql：where和on之间的区别
algsup
11-20 2323
前言： 今日学习mysql连接查询，然后突然看到关键字on，不明白是啥意思。然后在网上查询和二哥的帮助下，了解了一些原因，记录如下。 where与on之间的区别： 1）on后面的是连接（join）条件，代表两个表建立关系所遵循的规则 2）where后面的可以看作是筛选条件，是对最终结果集进行过滤所遵循的规则 思考：在连接（join）两个表时可以不可以使用where字句呢？ 答案是肯定的...
MySQL的on和where的区别，实战注意事项
Sun1090889283的博客
10-19 4013
MySql中on和where的注意事项
MySQL--join中on和where的区别
weixin_39332582的博客
02-24 245
MySQL--join中on和where的区别
MySql 连接查询中 on 和 where 的区别
大灰狼的专栏
01-21 4363
大家可以先看MySQL：left join 避坑指南 看过之后再接着看下面 在多表查询时，ON和where都表示筛选条件，on先执行，where后执行。 on 后跟连接条件，然后再加的筛选条件只针对关联表（从表） 执行顺序：从表按照条件筛选，然后再进行连接；即先筛选再连接 where 则针对连接后产生的临时表进行筛选 执行顺序：先连接再筛选 区别： 外连接时，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。而where条件是在临时表生.
关联查询实践指南：on和where条件在Mysql中的应用场景
七一
12-05 1264
今天在开发中遇到一个问题，在两个表关联查询时，最终得到的结果和预想结果不一致，经过排查后，我发现自己犯了一个很低级的错误，这样会导致查询出来的结果莫名其妙的变多或者变少。准备两张表dept,emp，一张员工表，一张部门表，内容如下；情况一：问题sql： 错误结果：这是因为右表的过滤条件放在where中会被过滤掉，而不会显示0条正确写法： 正确结果：情况二：问题sql： 错误结果：这是因为left join 做连接时，左表会显示全部数据，哪怕匹配不上为null的数据，左表过滤应当放入where条件中正确写法：
MySql中On和Where的区别
weixin_42976139的博客
03-09 5886
1、区别 ① 先执行on，后执行where；on是建立关联关系在生成临时表时候执行，where是在临时表生成后对数据进行筛选的。 ②比如两个表进行left join连接查询结果，where会对查询的结果进行过滤， on会返回驱动表的所有数据。 ③left join按照条件关联，不管是否为真，都返回左边表的数据。 2、on和where使用时机的分析 先抛开where条件，看On条件。left j...
MySQL - ON & WHERE 条件区别和执行顺序
牧码的博客
10-30 8876
一、案例 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下： 1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）...
MySQL查询语句中where与on的用法
weixin_51696882的博客
03-09 532
on在mysql查询语句中,是用在连接语句中的,例如:左外(left join),右外(right join).where是mysql查询语句中,用来过滤条件的,当where后的条件为真,则返回过滤后的信息。当使用on时,无论on后面的判断条件是非为真,都会显示左边表的信息。
mysql中left join设置条件在on与where时的用法区别分析
09-08
在MySQL中，LEFT JOIN操作用于将一个表（称为左表）的所有记录与另一个表（称为右表）中匹配的记录组合在一起。如果右表中没有匹配的记录，结果集将包含左表的所有记录，但右表对应列的值将为NULL。当我们设置LEFT ...
pillow_heif-0.17.0-pp39-pypy39_pp73-macosx_14_0_arm64.whl
07-27
基本介绍 名称与起源：Pillow，原名为PIL（Python Imaging Library），但PIL只支持Python 2版本。随着Python 3的普及，Pillow作为PIL的一个分支出现，兼容Python 3并提供更多的功能和改进。 主要功能：Pillow支持多种图像格式的打开、保存、显示以及基本的图像操作和处理，如裁剪、缩放、旋转、翻转、滤镜应用等。 跨平台性：Pillow库可以在不同的操作系统上运行，包括Windows、Linux和MacOS等。 主要功能模块 Pillow库包含多个功能模块，每个模块都提供了特定的图像处理功能。以下是一些常用的模块： Image：用于处理图像文件，提供打开、保存、调整大小、旋转、裁剪、滤镜等功能。 ImageDraw：提供在图像上绘制各种形状（如线条、矩形、圆形）和文本的功能。 ImageFont：用于加载和使用TrueType字体文件，以便在图像上绘制文本时设置字体样式、大小和颜色。 ImageFilter：提供各种滤镜效果，如模糊、锐化、边缘增强等，用于图像增强、特效处理和图像识别等应用。 ImageEnhance：用于调整图像的亮度、对比度、颜色饱和度等参数，使图像更加清晰、明亮或具有特定的调色效果。 高级功能 除了基本的图像处理功能外，Pillow还支持一些高级功能，如色彩空间转换、直方图均衡化等。这些功能可以帮助用户进行更复杂的图像处理和分析。
cykooz.resizer-3.0.0-cp310-cp310-macosx_11_0_arm64.whl
07-27
python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！
[毕设]Delphi题库管理与试卷自动生成系统.zip
07-27
[毕设]Delphi题库管理与试卷自动生成系统
131 种水果、蔬菜和坚果的 90380张的高质量图像数据集+图像识别与定位+该数据集相关的研究论文
07-27
<项目介绍> 包含131 种水果、蔬菜和坚果的 90380张的高质量图像数据集+该数据集相关的研究论文。可供深度学习图像识别与定位。 - 训练集大小：67700 个图像 测试集大小：22888 个图像 文件名格式：image_index_100.jpg（例如 32_100.jpg）或 r_image_index_100.jpg（例如 r_32_100.jpg）或 r2_image_index_100.jpg 或 r3_image_index_100.jpg。 “r”代表旋转水果。 “r2”表示水果绕第三轴旋转。 “100”来自图像尺寸（100x100 像素）。 水果是如何拍摄的: 1:使用 C920 摄像机拍摄水果。最好的网络摄像头之一。 2:在水果后面，我们放了一张白纸作为背景。 3:将水果和蔬菜种植在低速电机（3转/分钟）的轴中，并录制一段20秒的短片。 下载后请首先打开README.md文件（如有），仅供学习参考, 切勿用于商业用途。 --------
Matlab实现斑点鬣狗优化算法SHO-CNN-BiLSTM-Mutilhead-Attention多变量时序预测.rar
07-27
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
[毕设]HTTP服务器开发与设计.zip
最新发布
07-27
[毕设]HTTP服务器开发与设计
Mysql语句on和where的区别
05-25
`ON` 和 `WHERE` 关键字都可以在 MySQL 查询中用来指定过滤条件，但是它们的使用场景有所不同。 `ON` 关键字是用于在 `JOIN` 操作中指定连接条件的。`ON` 关键字用于在两个或多个表之间建立连接关系时指定连接条件...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值