手机版 欢迎访问it开发者社区(www.mfbz.cn)网站

当前位置: > 开发

数据库 SQL语句中的left join后on与where的区别

时间:2021/6/11 7:10:11|来源:|点击: 次

数据库 SQL语句中的left join后on与where的区别

  • 1.概述
  • 2.案例验证
    • 2.1 数据初始化
    • 2.2 查询验证
  • 3.总结分析

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

Copyright © 2002-2019 某某自媒体运营 版权所有