首先创建两个表
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:
SELECT
a.`name`,
a.`class`,
b.`subject`,
b.`scores`
FROM
student a
LEFT 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:
SELECT
a.`name`,
a.`class`,
b.`subject`,
b.`scores`
FROM
student a
LEFT 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里面包含右表的字段,那么左连接就会转变成内连接。这个因为sql执行优先级导致的问题,where的优先级是低于join链接的。
具体原因可以查看 MySQL left join操作中 on与where放置条件的区别