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

绝地灬酷狼 2022-10-06 04:43 121阅读 0赞

### 数据库 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;

![在这里插入图片描述][20210611065710107.png]

– left join on and 仍然查询出多条  
– 通过s.name 限制查询一条，结果查询出student全部，全部显示的只有符合条件的一条

select * from student s left join class c on s.class_id=c.id and s.name="张三";

![在这里插入图片描述][20210611065739664.png]

– left join on where 过滤查询出符合条件的一条

select * from student s left join class c on s.class_id=c.id where s.name="张三";

![在这里插入图片描述][20210611065800201.png]

# 3.总结分析 #

分析总结：
    
    数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
    
    在使用left join时，on 和 where 条件的区别如下：
    
    -- left join on and 
    on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回主表中的记录，只不多有些字段匹配不到显示为null。
    
    --  left join on where
    where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

[20210611065710107.png]: /images/20221005/744e7446eb984227a75c1dfbb3cc380f.png
[20210611065739664.png]: /images/20221005/5b8f2c12dcd14f979a998d35bc4c8420.png
[20210611065800201.png]: /images/20221005/d032457db2e94ffeb0bd97a3dd9a8a27.png

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读