[QA]MYSQL-LEFT-JOIN-右表有多行情况下-WHERE以右表为条件的查询

简述

Q: 两张表A,B是一对多关系，当A LEFT JOIN B 时，Where条件中有B,请问能否准确过滤出A中的数据？
A: 答案是可以的，记住“如果 WHERE 条件引用了右表的列，那么 LEFT JOIN 在条件中会表现得像 INNER JOIN”

举例

有两个表student， user_class表，分别代表学生与课程表，数据如下，需要过滤出学习语文和英语的学生名字，如何查出？
user:

user_class：

实战

1. 准备数据：

create table if not exists user (  
    id bigint not null primary key,  
    name varchar(64) not null  
);  
create table if not exists user_class (  
    id bigint not null primary key,  
    name varchar(64) not null,  
    student_id bigint not null  
);  
insert into user (id, name) values (1, '张三');  
insert into user (id, name) values (2, '李四');  
insert into user_class (id, name, student_id) values (1, '语文', 1);  
insert into user_class (id, name, student_id) values (2, '数学', 1);  
insert into user_class (id, name, student_id) values (3, '语文', 2);  
insert into user_class (id, name, student_id) values (4, '英语', 2);

2. 现在分别用user表左关联和内联user_class方式查出符合课程的用户。

sql1:

SELECT user.name FROM user LEFT JOIN user_class ON user.id = user_class.student_id WHERE user_class.name = '英语';  
SELECT user.name FROM user LEFT JOIN user_class ON user.id = user_class.student_id WHERE user_class.name = '语文';

结果为

李四
张三、李四

sql2:

SELECT user.name FROM user INNER JOIN user_class ON user.id = user_class.student_id WHERE user_class.name = '英语';  
SELECT user.name FROM user INNER JOIN user_class ON user.id = user_class.student_id WHERE user_class.name = '语文';

结果为

李四
张三、李四

结果都对。

LEFT JOIN 和 INNER JOIN 的查询结果相同。

总结

如果 WHERE 条件引用了右表的列（如示例中的 user_class.name），那么 LEFT JOIN 实际上会表现得像 INNER JOIN，因为任何右表中为 NULL 的行（即不匹配的行）都会被 WHERE 子句过滤掉。