MySQL Left Join不返回连接表的空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5426064/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Left Join not returning null values for joined table
提问by greg84
Please help me with the following MySQL query, which joins two tables (A and B):
请帮助我进行以下 MySQL 查询，该查询连接了两个表（A 和 B）：
SELECT * from A
left join B on A.sid = B.sid
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
AND (rYear = 2011 or rYear is null)
roleCodeis a field in table A and rYearis a field in table B
roleCode是表ArYear中的一个字段并且是表B中的一个字段
The result set is not as expected. Only 185 rows are returned, but there are 629 rows in table A that match the where condition. Shouldn't the rows without a matching row in table B be returned with null values for their B fields?
结果集不符合预期。只返回了 185 行，但 A 表中有 629 行符合 where 条件。表 B 中没有匹配行的行不应该返回其 B 字段的空值吗？
回答by Jage
You should not specify rYear in a WHERE clause. Those limit your results after the join. You should specify rYear in an ON clause to get back records with NULL from table B.
您不应在 WHERE 子句中指定 rYear。这些限制了您加入后的结果。您应该在 ON 子句中指定 rYear 以从表 B 中取回带有 NULL 的记录。
SELECT * from A
left join B
on A.sid = B.sid
AND (rYear = 2011 or rYear is null)
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
回答by RichardTheKiwi
Greg, is that really all there is to the query?
格雷格，这真的是查询的全部内容吗？
Sample tables
示例表
create table A(rCode int, sid int);
insert A select 1,1;
insert A select 2,3;
insert A select 3,2;
insert A select 5,4;
insert A select 1,5;
create table B(rYear int, sid int);
insert B select 2011,1;
insert B select null,3;
insert B select 2011,2;
insert B select 2015,2;
Queries:
查询：
SELECT * from A
left join B on A.sid = B.sid
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
AND (rYear = 2011 or rYear is null);
SELECT * from A
left join B on A.sid = B.sid AND (rYear = 2011 or rYear is null)
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5);
Both of the queries are exactly the same, both returning:
两个查询完全相同，都返回：
rCode sid rYear sid
----------- ----------- ----------- -----------
1 1 2011 1
2 3 NULL 3
3 2 2011 2
5 4 NULL NULL
1 5 NULL NULL
So I am surprised that Jage's query (the 2nd option) works for you but not your original. It would be different story without the inner or rYear is null.
所以我很惊讶 Jage 的查询（第二个选项）适用于您，但不适用于您的原始查询。如果没有内在，那将是另一回事or rYear is null。
Think of the LEFT JOIN like this [1]
想想像这样的 LEFT JOIN [1]
SELECT * from A
left join B on A.sid = B.sid
Keep everything in A, and where matched in the ON clause, keep B otherwise pad B columns with NULL. Add the WHERE clause [2]
将所有内容保留在 A 中，并且在 ON 子句中匹配的地方，保留 B 否则用 NULL 填充 B 列。添加 WHERE 子句 [2]
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
AND (rYear = 2011 or rYear is null);
Using the output from [1], CUT down based on the filter, applied AFTER the left join. With the rYear is null, it should still keep all A records, on the proviso that the rCode filter is matched. However, if the filter in rYear is only
使用 [1] 的输出，根据过滤器进行缩减，在左连接之后应用。使用rYear is null，它仍应保留所有 A 记录，条件是 rCode 过滤器匹配。但是，如果 rYear 中的过滤器仅
AND (rYear in (2011,2012))
It's a different story, because where B was not matched, the rYear was padded with NULL, which won't match the rYear filter -> the entire row gets removed, including the A record. Such a filter on rYear would have gone into the ON clause as shown below, otherwise might as well make it an INNER JOIN.
这是一个不同的故事，因为在 B 不匹配的地方，rYear 被填充为 NULL，这与 rYear 过滤器不匹配 -> 整行都被删除，包括 A 记录。rYear 上的此类过滤器将进入 ON 子句，如下所示，否则也可能使其成为 INNER JOIN。
SELECT * from A
left join B on A.sid = B.sid AND (rYear in (2011,2012))
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
