1.语法概述
前天写SQL时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条,奈何发现还是有两条。
后来发现 join on and 不会过滤结果记录条数,只会根据and后的条件是否显示 B表的记录,A表的记录一定会显示。
不管and 后面的是A.id=1还是B.id=1,都显示出A表中所有的记录,并关联显示B中对应A表中id为1的记录或者B表中id为1的记录。
2.测试验证
2.1 数据初始化
--------------------------
-- test left join on
--------------------------
-- student
CREATE TABLE `student` (
`Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`class_id` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`student`(`Id`, `name`, `age`, `class_id`) VALUES (1, '张三', 18, 1);
INSERT INTO `test`.`student`(`Id`, `name`, `age`, `class_id`) VALUES (2, '李四', 25, 2);
INSERT INTO `test`.`student`(`Id`, `name`, `age`, `class_id`) VALUES (3, '王五', 26, 3);
INSERT INTO `test`.`student`(`Id`, `name`, `age`, `class_id`) VALUES (4, '赵六', 30, 4);
-- class
CREATE TABLE `class` (
`Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`class`(`Id`, `name`) VALUES (1, '一年级');
INSERT INTO `test`.`class`(`Id`, `name`) VALUES (2, '二年级');
INSERT INTO `test`.`class`(`Id`, `name`) VALUES (3, '三年级');
INSERT INTO `test`.`class`(`Id`, `name`) VALUES (4, '四年级');
-- 验证初始化数据
select * from student s;
select * from class c;
2.2 查询验证
– student为主表关联class表
select * from student s left join class c on s.class_id=c.id;
– left join on and 仍然查询出多条
– 通过s.name 限制查询一条,结果查询出student全部,全部显示的只有符合条件的一条
select * from student s left join class c on s.class_id=c.id and s.name="张三";
– left join on where 过滤查询出符合条件的一条
select * from student s left join class c on s.class_id=c.id where s.name="张三";
3.总结分析
分析总结:
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left join时,on 和 where 条件的区别如下:
-- left join on and
on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回主表中的记录,只不多有些字段匹配不到显示为null。
-- left join on where
where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。