mysql join之：on条件和 where条件对比

最新推荐文章于 2024-06-17 19:27:08 发布

会spark的篮球少年

最新推荐文章于 2024-06-17 19:27:08 发布

阅读量1.9k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_15230053/article/details/82593406

版权

mysql 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

#table a
drop table if exists tablea;
CREATE TABLE tablea (
`name` varchar(11) COMMENT '姓名',
`score` int(11) COMMENT '分数',
`salary` int(11) COMMENT '分数',
`create_time` datetime COMMENT '读书时间',
`class` int(4) COMMENT '班级',
`role` varchar(20) COMMENT '英雄类型',
`id` int(10) primary key not null auto_increment COMMENT '流水自增'
);
INSERT INTO tablea VALUES ("周芷若",12,2000,'2018-1-2',1,'法师',null);
INSERT INTO tablea VALUES ("赵敏",14,4000,'2018-2-2',1,'射手',null);
INSERT INTO tablea VALUES ("李莫愁",19,3000,'2018-1-3',1,'坦克',null);
INSERT INTO tablea VALUES ("梅超风",10,4000,'2018-1-4',1,'战士',null);
INSERT INTO tablea VALUES ("殷素素",3,3000,'2018-1-6',1,'法师',null);
INSERT INTO tablea VALUES ("黄杉女",6,1000,'2018-1-6',1,'法师',null);
INSERT INTO tablea VALUES ("林诗音",3,1000,'2018-1-6',1,'法师',null);
INSERT INTO tablea VALUES ("天山童姥",3,1000,'2018-2-6',1,'战士',null);
select * from tablea

#table b
drop table if exists tableb;
CREATE TABLE tableb (
`name` varchar(11) COMMENT '姓名',
`score` int(11) COMMENT '分数',
`salary` int(11) COMMENT '分数',
`create_time` datetime COMMENT '读书时间',
`class` int(4) COMMENT '班级',
`boy_friend` varchar(20) COMMENT '英雄类型',
`id` int(10) primary key not null auto_increment COMMENT '流水自增'
);
INSERT INTO tableb VALUES ("周芷若",12,2000,'2018-1-2',1,'张无忌',null);
INSERT INTO tableb VALUES ("赵敏",14,4000,'2018-2-2',1,'宋青书',null);
INSERT INTO tableb VALUES ("李莫愁",19,3000,'2018-1-3',1,'成昆',null);
INSERT INTO tableb VALUES ("梅超风",10,4000,'2018-1-4',1,'金毛狮王',null);
INSERT INTO tableb VALUES ("殷素素",3,3000,'2018-1-6',1,'青翼蝠王',null);
INSERT INTO tableb VALUES ("黄杉女",6,1000,'2018-1-6',1,'阳顶天',null);
INSERT INTO tableb VALUES ("黄杉女",12,1000,'2018-1-6',1,'逍遥子',null);
select * from tableb

#测试证明：a left join b where是对结果join后的行数进行筛选
select a.*,b.boy_friend
from tablea a
left join tableb b on a.name=b.name
where b.boy_friend is not null and

#测试证明：a left join b on后面b的条件是先筛选b然后跟a join不符合条件的数据不join
select a.*,b.boy_friend
from tablea a
left join tableb b on a.name=b.name and b.score>10

#测试证明：a left join b on后面a的条件是先筛选a然后跟b join重点:不符合条件的之依然保留行
#说白了 on后面的条件只负责匹配不匹配，对主表的行数不影响，where 才是最后的筛选
select a.*,b.boy_friend
from tablea a
left join tableb b on a.name=b.name and a.score>10