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的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。