mysql left join on过滤_Mysql 的join on上的过滤和在where上过滤的区别

最新推荐文章于 2024-04-27 16:38:37 发布
最新推荐文章于 2024-04-27 16:38:37 发布
阅读量1.5k 收藏 2
点赞数 2
文章标签： mysql left join on过滤
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35223524/article/details/113908376
版权

测试如下：

(1)创建两张表，并插入数据，sql语句如下：

a表：

CREATE TABLE `a` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT '',

`grade` int(11) DEFAULT NULL,

`dept` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB CHARSET=utf8mb4

insert into `a` (`id`, `name`, `grade`, `dept`) values('1','LIJIE1','100','10');

insert into `a` (`id`, `name`, `grade`, `dept`) values('2','LIJIE2','90','20');

insert into `a` (`id`, `name`, `grade`, `dept`) values('3','LIJIE3','60','10');

insert into `a` (`id`, `name`, `grade`, `dept`) values('4','LIJIE4','80','10');

insert into `a` (`id`, `name`, `grade`, `dept`) values('5','LIJIE5','70','20');

b表：

CREATE TABLE `b` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`NAME` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB CHARSET=utf8mb4

insert into `b` (`id`, `NAME`) values('10','IT');

insert into `b` (`id`, `NAME`) values('20','IT2');

表数据显示：

a表：

id  name    grade   dept

1   lijie1  100     10

2   lijie2  90      20

3   lijie3  60      10

4   lijie4  80      10

5   lijie5  70      20

b表：

id  name

10  IT1

20  IT2

(2)left join 中on和where条件的对比

1.两张表join并且筛选分数大于等于80的，条件放在join on上面

select

A.id,A.name,A.grade,A.dept,B.id,B.name

from

A left outer join B

on

A.dept = B.id

and

A.grade >=80

查询结果：

cb164026f408c3fdd89e6f35f4823b28.png

2.两张表join并且筛选分数大于等于80的，条件放在where上面

select

A.id,A.name,A.grade,A.dept,B.id,B.name

from

A left outer join B

on

A.dept = B.id

where

A.grade >=80

查询结果：

9085066629a90df664b170a31c4400af.png

结论

当把过滤条件写在left join on上面会让基表所有数据都能显示(不管是否在on条件中如何限定了基表，最多只对基表进行分组，而不是过滤，即不论on条件中如果限制基表，基表的记录都会显示，但是on中会过滤非基表的记录)不满足条件的右表会以null填充，当过滤条件写在where上只会让符合筛选条件的数据显示。

多表join时条件写在where和on的区别(总结篇)

在开发过程中经常遇到这种情况：

多表关联join时，到底限制的条件是写在where后面效率高还是写在on后面，又或者是先对表过滤使表的数据量减少，到底这三种效率哪种更高，看了一堆网上说的，都没有说到具体点上，现在对这三种情况专门做以下详细说明，你就会明白到底是怎么回事了

干货总结：(以下只适用于left join，right join，full join，不适合inner join)

1、left join where + 基表过滤条件：先对基表执行过滤，然后进行left join；

2、left join where + 被关联表过滤条件：先执行left join，然后执行关联表的过滤条件；

3、left join on+基表过滤条件：满足过滤的基表记录执行left join，不满足的基表记录后面补null，然后两集合并一起；

4、left join on+被关联表过滤条件：先执行被关联表的过滤条件，然后执行left join；

示例：

sql:

CREATE TABLE app_test_01 (

id INT AUTO_INCREMENT PRIMARY KEY,

city VARCHAR(50) DEFAULT ''

)

INSERT INTO app_test_01 VALUES

(NULL,'北京'),(NULL,'上海'),(NULL,'深圳'),(NULL,'上海'),(NULL,'湖南'),(NULL,'湖北'),(NULL,'武汉');

CREATE TABLE app_test_02 (

id INT AUTO_INCREMENT PRIMARY KEY,

stu VARCHAR(50) DEFAULT '',

city VARCHAR(50) DEFAULT ''

)

INSERT INTO app_test_02 VALUES

(NULL,'一','北京'),(NULL,'二','北京'),

(NULL,'三','上海'),(NULL,'四','北京'),

(NULL,'五','深圳'),(NULL,'六','深圳'),

(NULL,'七','湖南'),(NULL,'八','湖北');

on和where对比：

一、第一种情况：

(1)表条件写在where后面:

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

WHERE a.`city` = '深圳' ;

结果：

15358f9b54c34bd2a40ef011fe77a09b.png

(2)先对基表进行过滤，然后关联

SELECT

a.id,

b.stu

FROM

(SELECT

id

FROM

app_test_01

WHERE city = '深圳') a

LEFT JOIN app_test_02 b

ON a.id = b.id ;

结果：

a79fccdfe07f5731a2f76999d34ae155.png

这两种写法的执行顺序是一样的，都是先执行过滤，然后执行关联；所以运行效率是一样的！

二、第二种情况：

(1)where条件放基表

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

WHERE a.city = '深圳';

结果：

09a18c60a4616d28800aa384af760e6a.png

(2)where条件放关联表

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

WHERE b.city = '深圳' ;

结果：

6d3518fdbbada608931c3e564c860b04.png

第一种执行顺序：<1>先对a表进行where过滤，<2>再对过滤后的a表与b表进行关联

第二种执行顺序：<1>先a表和b表进行关联，<2>再对关联的结果执行where后面b表的条件

三、第三种情况

(1)第一种：基表过滤条件写where后面

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

WHERE a.city = '深圳' ;

结果：

741154cb3dbcb5b1b619f671ee50c1eb.png

(2)第二种：基表条件写on后面

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

AND a.city = '深圳' ;

结果：

106b14ba4ee7eaf646f800ecddec0365.png

(3)第三种情况：基表过滤条件和被关联表的过滤条件都写在on后面

SELECT

a.id,

b.stu

FROM

app_test_01 a

LEFT JOIN app_test_02 b

ON a.id = b.id

AND a.city = '深圳'

AND b.`city` = '深圳'

结果：

65c496953636a92a85dc9025697e3116.png

第一种执行顺序是：

<1>先对a表执行过滤条件，

<2>然后过滤后的a表和b表进行关联；

第二种执行顺序：

<1>先使用a.city='深圳'的过滤条件将a表分为两部分，一部分满足过滤条件，一部分不满足过滤条件(即on后面基表的条件只是用来和被关联表进行关联)，

<2>对满足条件的与b表关联，不满足条件的后面字段补null，然后将满足和不满足的两部分集union起来成最后结果集；

第三种执行顺序：

<1>先对b表进行b.city=‘上海’条件对b表进行过滤，

<2>使用a.city='深圳'条件将a表分为满足和不满足条件的两部分集

<3>对满足集合与过滤后的b表进行关联，不满足集后面字段直接补null，最后将两个集合union起来成最终结果集

示例：

SELECT l.id FROM

eprj_price_material l ,eprj_price_common p,eprj_list_norm_consumption c,eprj_list_norm_items m

WHERE c.`EPRJ_LIST_NORM_ITEMS_ID` = m.id AND c.`EPRJ_PRICE_COMMON_ID` = p.`ID`

AND l.`EPRJ_PRICE_COMMON_ID` = p.id

AND m.id = 886168

定额表eprj_list_norm_items中有多个消耗eprj_list_norm_consumption，每个消耗对应一种实物eprj_price_material，每个实物属于唯一一个工料机eprj_price_common，每个实物在运输途中可能有关税eprj_export_customs_ei，也可能没有关税，如何查找出没有关税的实物？

1.最初的做法：

SELECT ei.EPRJ_PRICE_MATERIAL_ID FROM

(eprj_price_material l,eprj_price_common p,eprj_list_norm_consumption c,eprj_list_norm_items m) LEFT JOIN eprj_export_customs_ei ei

ON c.`EPRJ_LIST_NORM_ITEMS_ID` = m.id AND c.`EPRJ_PRICE_COMMON_ID` = p.`ID` AND ei.`EPRJ_PRICE_MATERIAL_ID` = l.`ID`

AND l.`EPRJ_PRICE_COMMON_ID` = p.id

WHERE m.id = 886168

这样属于大表驱动小表，查询时间过长，半天没查询出结果，没去看查询结果了。。。

2.以小表驱动大表，把主表作为材料表，其余表以括号放一起，作为从表：

SELECT DISTINCT l.id,m.id FROM

eprj_price_material l LEFT JOIN (eprj_price_common p,eprj_list_norm_consumption c,eprj_list_norm_items m,eprj_export_customs_ei ei)

ON c.`EPRJ_LIST_NORM_ITEMS_ID` = m.id AND c.`EPRJ_PRICE_COMMON_ID` = p.`ID` AND ei.`EPRJ_PRICE_MATERIAL_ID` = l.`ID`

AND l.`EPRJ_PRICE_COMMON_ID` = p.id AND m.id = 886168

WHERE m.id = 886168

这样查询速度很快，但是遗憾的是，只查询出有关税的材料，因为作为从表，以()放一起的话，那么()中的从表之间是内连接，即从表在以on条件过滤之后，只剩下有关税的工料机，再由工料机和主表关联，做join，然后通过where条件过滤，只剩下有关税的材料。。。

3.最后一种，以子查询，先查询出在该定额下的消耗，然后以该消耗的查询结果作为子查询，并以子查询做为主表，以关税作为从表，顺利查询出结果，速度还可以：

SELECT t.id,ei.`id`,ei.EPRJ_PRICE_MATERIAL_ID

FROM

(SELECT l.id,l.`EPRJ_INFO_ID` FROM

eprj_price_common p,

eprj_list_norm_consumption c,

eprj_list_norm_items m,

eprj_price_material l

WHERE

c.`EPRJ_LIST_NORM_ITEMS_ID` = m.id

AND c.`EPRJ_PRICE_COMMON_ID` = p.`ID`

AND l.`EPRJ_PRICE_COMMON_ID` = p.id

AND m.id = 886168 ) t

LEFT JOIN eprj_export_customs_ei ei ON ei.`EPRJ_PRICE_MATERIAL_ID` = t.id

WHERE ei.`eprj_info_id` IS NULL;

所以在做主表的时候可能考虑先筛选作为子查询，然后再作为主表(因为主表在join的时候不会过滤的，只会分组，符合分组的时候在进行join的时候就放入匹配的从表记录；不符合分组在从表数据出就放入null，所以在join的时候，如果有必要，可以先把主表使用子查询过滤一下，筛选出符合条件的记录，再使用left join)

以上都是经过查看执行计划并且经过具体测试得出的结论，所以针对不同的业务场景可以选择不同的写法来提高执行效率。

我相信有了上面几种情况的掌握，无论在怎么添加条件，都能很快的判断出代码的执行顺序！！！

我不是庄俊男
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
遇到问题----mysql---同时使用left join和null会缺少数据
直到世界的尽头
09-05 1万+
情况我们有一个复杂的查询语句 会多重组合嵌套。其中会使用WHERE ... IS NULL 以及 left join。结果发现会缺少一部分符合我们条件的数据。改成inner join后 又可以查询出来。原因推荐看这两个帖子:http://bbs.csdn.net/topics/390582391http://virusfu.iteye.com/blog/1734065抄录帖子如下:关于 “A LE
解析mysql left( right ) join使用on与where筛选的差异
10-27
本篇文章是对mysql left( right )join使用on与where筛选的差异进行了详细的分析介绍，需要的朋友参考下
mysql在join条件时筛选掉和使用where语句筛选掉的区别
10-26 233
使用join时筛选掉，在left join时不会让连表前的结果丢失，而使用where会让连表前的的结果也丢失
3. join操作时，过滤条件写在on和where的异同？
最新发布
doing a 责任心 dataer
04-27 508
数据库在通过连接两张或多张表返回数据时，均会先生成一张中间的临时表，然后再将临时表返回给用户。left join 和 right join 场景中，过滤条件写在on和where。inner join 场景中，过滤条件写在on和where。
在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。
weixin_47397751的博客
01-19 508
转载自:https://blog.csdn.net/cainiao1923/article/details/103594153 通过上面的问题现象和分析，可以得出了结论：在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。 SQL 看似简单，其实也有很多细节原理在里面，一个小小的混淆就会造成结果与预期不符，所以平时要注意这些细节原理，避免关键时候出错。 ...
hive的left join，条件放在on、whrere里的不同结果(含左右表各种情况）
格格颜心
04-25 2926
一、left join中where里放右侧表的过滤条件的后果：将左表满足条件的数据也过滤掉了。 原因：在总的where里放的条件是在生成的中间大宽表的结果上做的过滤 我亲身经历的一次事故：本意是要过滤各自的数据，然后做左关联，那么左表的数据自然是不希望被过滤掉的，要不然就用join了。 但是不理解where条件的执行顺序，将右表的过滤条件也放在里where后面，结果丢失了大量的左表数据，做了次事故报告。 涨的姿势：如果要过滤右表的数据，要么放在右表的子查询里，要么放在on里。 二、left joi
left join查询on后面的条件过滤失效的问题
weixin_42159686的博客
01-17 2951
新建两种表：用户表 tb_user 和订单表 tb_orderlist, 订单表中的uid字段关联用户表中的id字段 1，直接在left join后面的on中加关联关系和条件过滤，查询结果如下图,可以看到on后面的条件过滤： u.id = 1并没有生效 SELECT u.id,u.name,u.age,o.order_number,o.uid FROM tb_user u LEFT JOIN tb_orderlist o ON u.id = o.uid AND u.id = 1 ,2，我们将 on后面
MySQL left join操作中on和where放置条件的区别介绍
12-15
总结一下，`ON` 和 `WHERE` 在 `LEFT JOIN` 中的主要区别在于： 1. `ON` 用于在生成临时表时定义连接条件，无论条件是否满足，都会返回左表的所有行。 2. `WHERE` 在临时表生成后进行过滤，如果它包含了连接条件，...
mysql中left join设置条件在on与where时的用法区别分析
09-08
当我们设置LEFT JOIN的条件时，可以在ON和WHERE子句中指定，但它们之间存在一些关键区别。 首先，理解ON子句的作用至关重要。ON子句中的条件是在连接左右两表时使用的，它定义了两个表之间的关系。在这个例子中，`...
hive&sql-LEFT JOIN之后，数据量为啥比左表还要少
DCHAO的博客
07-13 3566
a表 LEFT JOIN B表，结果表的数据量正常应该是=a表或者>a表。但有种情况，结果表会比a表少。 逻辑很简单，就是用两个表同一天的分区数据做关联，但是结果却比 a 表的数据少了。当把唯一一个 “where” 换成 “and” 后，结果便正确了。这个原因其实是过滤数据的对象不同。 下面用 MySQL 创建示例来进行说明， 只用on，是我们想要的 结果还是以左表为准，只是右边只有 dept_id = 2 的数据。 所以，LEFT JOIN时，无论ON的条件如何，左表不会被过滤，只会过滤右表。ON仅
MySQL LEFT JOIN (where is null 否定 on里面条件)
万里船的专栏
09-05 7278
即使你认为自己已对 MySQL 的 LEFT JOIN 理解深刻，但我敢打赌，这篇文章肯定能让你学会点东西！ ON 子句与 WHERE 子句的不同 一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法 Matching-Conditions 与 Where-conditions 的不同 关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒
MySQL 从查询结果对比 join 和 where 的区别
weixin_53008424的博客
04-20 1633
MySQL 从查询结果对比 join 和 where 的区别 写在前面 : 本文仅从使用效果上对比join和where的区别，不涉及底层执行原理； 本文为工作中的自我总结，没有权威来源； 从使用习惯上来讲，join用于连表之后过滤掉无效数据，where用于过滤满足条件的数据； 但是从效果来说，好像join之后的条件写在where之后也可以有相同的效果，where之后的条件写在join之后也没有什么很大的区别； 俺记得 sql 的92语法在连表查询时都是不用 join 的，直接使用where过滤无
MYSQL左连接查询过滤问题
weixin_49231692的博客
05-16 1568
1、MySQL left jion 连接查询，保留左边查询记录信息在on连接后面加过滤条件（连接右表，有则查值，无则为null） 2、where 条件过滤，过滤字段记录若为null，也会被过滤掉，例如 ：查询姓名不等于王伟的同学 select ＊ from student where name != “王伟” 这样写，名字为null的同学也会被过滤掉。 正确写法应该是: select * from student where name is null or name!=“王伟” 第二种正确写法： 使用ifn
在Windows和Linux系统下安装并配置JDK环境
m0_72758098的博客
04-12 309
1 各平台JDK的下载首先进行的是JDK的下载，Oracle官网的下载实在是太慢了，而且很难找到以往版本的JDK，所以这里我推荐一个博主自己搭的网站下载，里面有各个版本、各个平台的JDK。下载好的文件如下图：2 后续步骤1）Windows系统2.1 安装JDK在Windows下安装JDK的话只需要根据引导安装，不需要Linux下相对复杂的步骤，但要注意的一点是要记住自己安装好的Java目录。比如我安装的位置就是安装好的目录如下图所示：2.2 配置环境变量。
SQL关于left join的重复问题和连接条件where on筛选问题
Java后端技术栈
05-25 3217
datediff为null的时候，左表结果为7，右表为0，其他的以此类推，和前面的结果是一样的。这样我们就知道了，没有达到预期的根源在于存在空的日期。可以看到最终得到了想要的结果，以最后一行为例，它表示，距离2019-09-29 5天的那天(也就是2019-09-24)活跃的人数有5个，那些人在2019-09-29仍然活跃的有2人，你可以看一下明细数据核对一下。a表有6条记录，b表有3条记录，且关键的键是唯一的，因此最终结果以a表为准有6条记录，b表有3条关联不上，相应的记录中，b表所有的字段都为空。
LEFT JOIN ON过滤逻辑说明
wozengcong的专栏
09-29 3555
left join:以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左表无效 right join:以右表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对右表无效 full join:以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左右表无效 inner join:等值连接，根据过滤条件生成临时表。用inner join 后面的条件 可以用 w
mysql 语句中 left join 后过滤数据用 on 还是 where?有啥区别?
csdn1234561231的博客
03-20 2048
当使用 LEFT JOIN 进行连接时，ON 子句中的连接条件仅限制右表中匹配左表的记录，即 LEFT JOIN 返回的结果集中包含左表中所有的记录，但是右表中没有匹配的记录会被填充为 NULL 值。总体来说，使用 ON 子句限制 LEFT JOIN 连接的结果集，而使用 WHERE 子句对连接后的结果集进行过滤。当使用 INNER JOIN 进行连接时，ON 子句中的连接条件仅返回左表和右表中匹配的记录，即 INNER JOIN 返回的结果集中仅包含左表和右表中都存在的记录。
SQL中使用JOIN时，过滤条件放在on和where中的区别
大柳的博客
02-15 5949
对于不同的JOIN类型，过滤语句放在子查询、on或者where中，有时候结果以及效率差别很大的，记录一下正确用法。
外连接left join 过滤条件选择是放on条件中还是where条件中
不想搞技术的的cc不是好cc
01-25 766
一、问题描述 今天在使用left join进行连接查询的时候，由于过滤条件写错了导致查询的结果与自己预期想要达到的结果不一致，在这里通过一个简单的demo记录一下。 二、demo 现在有两张表，一张班级表，一张学生表。如下图所示： 需求一：找出每个班级的名称及其对应的⼥同学数量 需求二：找出⼀班的同学总数 三、错误的sql查询 对于需求一我不假思索的写出了如下sql语句： SELECT c.name, count(s.name) as num FROM classes c left join
update left join mysql_mysql update left join on 多表关联更新和where语句
05-17
在上面的例子中，我们使用了 `LEFT JOIN` 来关联 `table1` 和 `table2` 表，并将它们的 `id` 列作为关联条件。然后使用 `SET` 语句来更新 `table1` 表中的 `column1` 列。注意，这里使用的是 `table1.column1`，而...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值