SQL中 left join on 后用 and 还是 where的区别

1.环境准备

首先创建两个表并添加基本的 数据

CREATE TABLE `clazz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT 班级名称,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT 学生姓名,
  `age` int(11) DEFAULT NULL COMMENT 年龄,
  `clazzId` int(11) DEFAULT NULL COMMENT 班级id,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

insert into clazz values(1,计算机1班),(2,计算机2班),(3,计算机3班);
insert into student values(null,赵敏,22,1),(null,张明,20,3),(null,李慧,21,3),(null,赵美美,20,2),(null,贺峰,25,2),(null,孙强,19,2);

2.实战说明

2.1基本查询

使用left join查询所有学生的信息及所属的班级:

select t1.*,t2.name from student t1 left join clazz t2 on t1.clazzId=t2.id

查询的结果如下图:

SQL中 left join on 后用 and 还是 where的区别

2.2使用and查询

需求:只查询名字是"李慧"的学生信息和班级。预期结果:查询的数据只有一条。

使用and进行查询的sql语句如下:

select t1.*,t2.name from student t1 left join clazz t2 on t1.clazzId=t2.id and t1.name=李慧

查询的结果:

SQL中 left join on 后用 and 还是 where的区别

可以看出,虽然把该同学的信息都查询出来了,但其他学生信息也查询出来了,只是他们的班级信息是空的,不符合预期。

2.3使用where查询

需求:只查询名字是"李慧"的学生信息和班级。预期结果:查询的数据只有一条。

使用and进行查询的sql语句如下:

select t1.*,t2.name from student t1 left join clazz t2 on t1.clazzId=t2.id where t1.name=‘李慧‘

查询的结果:

SQL中 left join on 后用 and 还是 where的区别

此时查询的结果和预期是一样的,符合条件。

2.4原理分析

通过上述在left join on后使用and或where的演示,分析如下:

and 条件是在生成临时表时使用的条件,不管条件是否为真,都会返回左边表中的记录。适用于查询左表的所有数据的情况!

where 条件是在临时表生成好后,再对临时表进行过滤的条件,条件为真时才会返回左表中的记录。适用于查询左表中符合条件的数据的情况!

 

left join,right join,full join的特殊性和left join 是类似的,而而inner jion没这个特殊性。

SQL中 left join on 后用 and 还是 where的区别

上一篇:Oracle-DG最大可用模式下,dg备库可能对主库有什么影响?


下一篇:SQL Server 备份数据库