on和where的区别

最近开发自测时，在mybatis写的xml中，写了一条on与where中都有判断条件的sql（判断条件使用<if></if>拼接）。在执行sql中发现一条没有where条件，判断都在on中的sql，导致了错误的查询结果

---

为什么on中的判断条件和where中的判断条件执行效果不同呢？这就涉及到了join以及on的特性。

join是用来连接表的， 而on是表之间连接的条件

• left join/right join/full join，在用on连接时，不管on上的条件是否为真都会返回left或right表中的记录
• inner join,在使用on连接时，会返回两表的交集
• join会生成临时表

做一个测试，先是测试使用的sql脚本

create table test_master(
    id bigint auto_increment primary key ,
    name varchar(20) default '' not null comment '名称',
    code varchar(20) default '' not null comment '编码'
) comment '测试主表';

insert into test_master(name,code) values('张三','t001'),('李四','t002'),('王五','t003'),('赵六','t004'),('田七','t005');

create table test_child(
    id bigint auto_increment primary key ,
    master_id bigint not null comment '主表id',
    content varchar(200) not null comment '测试内容',
    create_time datetime not null comment '创建时间'
) comment '测试子表';

insert into test_child(master_id, content, create_time) VALUES (1,'这个是测试1的子表',now()),(1,'这也是个测试1的子表',now()),(2,'这是个测试2的子表',now())

create table test_detail(
    id bigint auto_increment primary key ,
    child_id bigint not null comment '子表id',
    detail varchar(200) not null comment '测试细节',
    result tinyint(2) null comment '测试结果 1-测试成功 0-测试失败',
    test_time datetime null comment '测试时间'
) comment '测试细节表';

insert into test_detail(child_id, detail, result, test_time)
VALUES(1,'测了个寂寞',1,now()),(1,'测了个荒凉',0,now()),(1,'到底测没测',null,null),(2,'测了吧',0,now());

分别执行on中有判断条件，以及where中有判断条件的sql

可见确实查询的结果不同，on中多了child.id是空的空数据。即 将test_child中筛选的数据绑在了test_detail上，生成了中间表，返回给了用户；
而使用where是对join之后生成的中间表进行筛选。

让我们再看一下sql的Explain Plan

中间表的行数都是4行。

由于on中写判断条件时，中间表有部分空列（在join的表中做过筛选），当数据比较多时，会比on中不写判断条件时生成的中间表，占用更小的空间。

该怎么选择判断条件的位置

看到on的特性，以及sql执行的结果，很多小伙伴已经知道，该怎么让数据正确的显示出来了吧。

比如left join test_child child，判断条件中 child.id = 1放在on中，child.content like '%测%'放在where中，就可以做到 提高效率 且 保证sql执行的正确性

***所以：小伙伴们，在on中使用判断条件时，一定要谨慎！！！***

---