MySQL 左连接查询返回重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18033683/
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
Left Join query returns duplicates
提问by DominicM
I have additives table:
我有添加剂表：
id name
30 gro
31 micro
32 bloom
33 test
And stage_additives table:
和 stage_additives 表：
stage_id additive_id dose
195 30 2
195 31 3
195 32 1
Mysql query:
mysql查询：
SELECT a.id,
a.name,
sa.dose
FROM additives a
LEFT JOIN stage_additives sa
ON sa.stage_id = 195
Result is:
结果是：
id name dose
32 Bloom 2
32 Bloom 3
32 Bloom 1
30 Gro 2
30 Gro 3
30 Gro 1
31 Micro 2
31 Micro 3
31 Micro 1
33 test 2
33 test 3
33 test 1
This does not make sense to me as there ore 3 of each item in the result even though there is only one item per each table with same id/name.
这对我来说没有意义，因为即使每个表只有一个具有相同 ID/名称的项目，结果中的每个项目也有 3 个。
I also tried inner join, right join but result is almost identical except for order.
我也试过内连接，右连接，但结果几乎相同，除了顺序。
What I want is all id, name from additives and dose from stage_additives if it exists otherwise NULL (or better still custom value of 0)
我想要的是所有 id、来自添加剂的名称和来自 stage_additives 的剂量（如果存在）否则为 NULL（或更好的自定义值为 0）
回答by Gordon Linoff
You are missing the condition in your left join:
您缺少以下条件left join：
SELECT a.id,
a.name,
sa.dose
FROM additives a
LEFT JOIN stage_additives sa
ON a.id = sa.additive_id and sa.stage_id = 195;
Remember, the joinis conceptually doing a cross joinbetween the two tables and taking only the rows that match the oncondition (the left joinis also keeping all the rows in the first table). By not having an oncondition, the join is keeping all pairs of rows from the two tables where sa.stage_id = 195-- and that is a lot of pairs.
请记住，从join概念上讲cross join，在两个表之间执行 a并仅获取与on条件匹配的行（left join也将所有行保留在第一个表中）。由于没有on条件，连接将保留两个表中的所有行对，其中sa.stage_id = 195- 这是很多对。
EDIT:
编辑：
(In response to moving the condition sa.stage_id = 195into a whereclause.)
（响应将条件移动sa.stage_id = 195到where子句中。）
The condition sa.stage_id = 195is in the onclause on purpose. This ensures that the left joinactually behaves as written. If the condition were moved to a whereclause, then the left joinwould turn into an inner join. Rows from additivewith no match in stage_additivewould have a NULLvalue for sa.stage_idand be filtered out. I have to assume that the OP intended for the left jointo keep all rows in additivebecause of the explicit use of left join.
条件sa.stage_id = 195是on故意在子句中。这确保了left join实际行为如所写。如果将条件移到where子句中，则 theleft join将变成inner join. 来自additive没有匹配项的stage_additive行将有一个NULL值sa.stage_id并被过滤掉。我必须假设 OP 旨在left join保留所有行，additive因为left join.
回答by kwarunek
in clause ONshould be relation between tables and condition for stage_id in WHERE
in 子句ON应该是 stage_id in 的表和条件之间的关系WHERE
回答by xanatos
You probably wanted something like
你可能想要类似的东西
SELECT a.id,
a.name,
sa.dose
FROM additives a
LEFT JOIN stage_additives sa
ON sa.additive_id = a.id
WHERE sa.stage_id = 195
SQLFiddle: http://sqlfiddle.com/#!2/fd607/4
SQLFiddle：http://sqlfiddle.com/#!2/fd607/4
In this case, instead of a LEFT JOINyou can use an INNER JOIN, because the WHEREclause is based on the success of the join.
在这种情况下，LEFT JOIN您可以使用 an代替 a INNER JOIN，因为该WHERE子句基于连接的成功。
SELECT a.id,
a.name,
sa.dose
FROM additives a
INNER JOIN stage_additives sa
ON sa.additive_id = a.id AND sa.stage_id = 195
SQLFiddle: http://sqlfiddle.com/#!2/fd607/8
SQLFiddle：http://sqlfiddle.com/#!2/fd607/8
But perhaps you really wanted a LEFT JOIN:
但也许你真的想要一个LEFT JOIN：
SELECT a.id,
a.name,
sa.dose
FROM additives a
LEFT JOIN stage_additives sa
ON sa.additive_id = a.id AND sa.stage_id = 195
This returns a fourth row:
这将返回第四行：
33 test (null)
SQLFiddle: http://sqlfiddle.com/#!2/fd607/7
SQLFiddle：http://sqlfiddle.com/#!2/fd607/7
