SQL LEFT OUTER JOIN（给出额外的行）问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1713932/
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 OUTER JOIN (gives extra rows) problem
提问by Theofanis Pantelides
I have two tables which I want to join together using a left outer join. However, even though my left table contains only unique values, the right table satisfies the CONDITION more than once and as such, adds extra rows to the resultset.
我有两个表，我想使用左外连接将它们连接在一起。但是，即使我的左表只包含唯一值，右表也不止一次满足条件，因此向结果集添加了额外的行。
Code to replicate problem:
复制问题的代码：
declare @tb1 table (c1 int) declare @tb2 table (c2 int) INSERT INTO @tb1 VALUES (1) INSERT INTO @tb1 VALUES (2) INSERT INTO @tb1 VALUES (3) INSERT INTO @tb1 VALUES (4) INSERT INTO @tb2 VALUES (3) INSERT INTO @tb2 VALUES (4) INSERT INTO @tb2 VALUES (5) INSERT INTO @tb2 VALUES (6) select * from @tb1 left outer join @tb2 ON c1 = c2 INSERT INTO @tb2 VALUES (3) INSERT INTO @tb2 VALUES (4) INSERT INTO @tb2 VALUES (5) INSERT INTO @tb2 VALUES (6) select * from @tb1 left outer join @tb2 ON c1 = c2
As you can see the first SELECT returns 4 rows, where the second SELECT 6, although the left table remains unchanged.
如您所见，第一个 SELECT 返回 4 行，其中第二个 SELECT 6，尽管左表保持不变。
How does one stay strict to the left table, and only use the right table to COMPLEMENT the rows from the left table?
如何对左表保持严格，只使用右表来补充左表中的行？
HELP!
帮助！
RESULTS: c1 c2 ----------- ----------- 1 NULL 2 NULL 3 3 4 4 [DUPLICATE @tb2 records] c1 c2 ----------- ----------- 1 NULL 2 NULL 3 3 3 3 4 4 4 4
回答by Tor Haugen
Sorry, but your thinking is skewed.
对不起，但你的想法是歪曲的。
Think about it this way: if you only want one single row from tb2 for each row in tb1, which one should the server choose? The fact is that from the definition of a join, every row in the right-hand-side table that matches the left-hand-side row is a match and must be included.
这样想一想：如果您只希望 tb2 中的一行用于 tb1 中的每一行，那么服务器应该选择哪一行？事实是，根据连接的定义，右侧表中与左侧行匹配的每一行都是匹配项，必须包括在内。
You'll have to ensure tbl2 has distinct values for c2 before the join. Murph's suggestion might do it, provided your SQL variant supports DISTINCT [column] (not all do).
在连接之前，您必须确保 tbl2 具有不同的 c2 值。Murph 的建议可能会这样做，前提是您的 SQL 变体支持 DISTINCT [column]（并非所有人都支持）。
回答by Adriaan Stander
Try useing
尝试使用
select DISTINCT * from @tb1 left outer join @tb2 ON c1 = c2
回答by davek
If you want to keep just single rows on the left hand side, you'll need to decide what you want to show on the right, for each unique value on the left. If you want to show a count, for example, you could do this:
如果您只想在左侧保留单行，则需要针对左侧的每个唯一值确定要在右侧显示的内容。例如，如果你想显示一个计数，你可以这样做：
select b1.c1, x.c from @tb1 b1
left outer join
(
select c2, count(*) as c
from @tb2
group by c2
) as x
ON b1.c1 = x.c2
or if you just want one occurence of values from c2:
或者，如果您只想从 c2 中出现一次值：
select b1.c1, x.c2 from @tb1 b1
left outer join
(
select c2
from @tb2
group by c2
) as x
ON b1.c1 = x.c2
回答by Murph
Hmm, the query is doing what its supposed to since there are duplicate records (or at least duplicate identifiers) in the right hand table.
嗯，查询正在做它应该做的事情，因为右手边的表中有重复的记录（或至少是重复的标识符）。
To get the effect you want something like:
为了获得你想要的效果：
SELECT * FROM @tb1 LEFT OUTER JOIN (SELECT DISTINCT c2 FROM @tb2) t2 ON @tb1.c1 = t2.c2
If that isn't sufficient you'll need to explain the requirement in a bit more detail.
如果这还不够，您需要更详细地解释要求。
回答by Gennady Shumakher
select distinct * from @tb1 left outer join @tb2 ON c1 = c2
从 @tb1 左外连接 @tb2 ON c1 = c2 中选择不同的 *
