MySQL左外连接where条件包含多表写法

来源：互联网发布：servlet获取表单数据id 编辑：程序博客网时间：2024/04/28 14:19

首先创建两个表

CREATE TABLE `student` (`name` VARCHAR (10) DEFAULT NULL,`class` INT (11) DEFAULT NULL)

CREATE TABLE `score` (  `name` varchar(10) DEFAULT NULL,  `subject` varchar(10) DEFAULT NULL,  `scores` int(11) DEFAULT NULL)

然后分别插入数据

insert into student values('Tom',1);insert into student values('Jerry', 2);insert into score values('Tom','Cat',100);insert into score values('Tom','Cat',99);insert into score values('Jerry','Mouse',80);

Sql1:

SELECTa.`name`,a.`class`,b.`subject`,b.`scores`FROMstudent aLEFT OUTER JOIN score b ON a.`name` = b.`name`WHERE(a.class in (1,2) and b.`subject` = 'Mouse')

返回的数据并不是left join，反而是inner join的数据

+-------+-------+---------+--------+| name  | class | subject | scores |+-------+-------+---------+--------+| Jerry |     2 | Mouse   |     80 |+-------+-------+---------+--------+

Sql2:

SELECTa.`name`,a.`class`,b.`subject`,b.`scores`FROMstudent aLEFT OUTER JOIN score b ON a.`name` = b.`name` and b.`subject` = 'Mouse'WHERE(a.class in (1,2) )

这个数据是正常的

+-------+-------+---------+--------+| name  | class | subject | scores |+-------+-------+---------+--------+| Jerry |     2 | Mouse   |     80 || Tom   |     1 | NULL    |   NULL || Tom   |     1 | NULL    |   NULL |+-------+-------+---------+--------+

由此可以看出来，当左外链接的时候，如果where里面包含右表的字段，那么左连接就会转变成内连接。这个应该是where的优先级最高导致的。如果真的需要过滤右表的字段，那么把条件写到on后面就可以了

0 0