LEFT OUTER JOIN(产生额外的行)问题 [英] LEFT OUTER JOIN (gives extra rows) problem

问题描述

我有两个表，我想使用左外部联接将它们联接在一起.但是，即使我的左表仅包含唯一值，右表也可以多次满足CONDITION，因此会向结果集添加额外的行.

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.

复制问题的代码:

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

您可以看到第一个SELECT返回4行，第二个SELECT 6返回，尽管左侧表格保持不变.

As you can see the first SELECT returns 4 rows, where the second SELECT 6, although the left table remains unchanged.

一个人如何严格遵守左表，而仅使用右表来补全左表中的行?

How does one stay strict to the left table, and only use the right table to COMPLEMENT the rows from the left table?

帮助！

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

推荐答案

对不起，但您的想法不正确.

Sorry, but your thinking is skewed.

这样想:如果您只希望tb1中的每一行都来自tb2中的一行，那么服务器应该选择哪一行?事实是，根据联接的定义，右侧表中与左侧行匹配的每一行都是匹配项，并且必须包括在内.

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.

在连接之前，您必须确保tbl2具有c2的不同值.只要您的SQL变体支持DISTINCT [column](并非全部)，Murph的建议就可以实现.

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).

这篇关于LEFT OUTER JOIN(产生额外的行)问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！