SQL LEFT OUTER JOIN 如何返回比左表中存在的记录更多的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/916414/
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
How can a LEFT OUTER JOIN return more records than exist in the left table?
提问by Jay Wilde
I have a very basic LEFT OUTER JOIN to return all results from the left table and some additional information from a much bigger table. The left table contains 4935 records yet when I LEFT OUTER JOIN it to an additional table the record count is significantly larger.
我有一个非常基本的 LEFT OUTER JOIN 来返回左表中的所有结果和一个更大表中的一些附加信息。左表包含 4935 条记录，但当我将其左外连接到另一个表时，记录数明显更大。
As far as I'm aware it is absolute gospel that a LEFT OUTER JOIN will return all records from the left table with matched records from the right table and null values for any rows which cannot be matched, as such it's my understanding that it should be impossible to return more rows than exist in the left table, but it's happening all the same!
据我所知，LEFT OUTER JOIN 将返回左表中的所有记录，右表中的匹配记录和任何无法匹配的行的空值，这是绝对的福音，因此我的理解是它应该不可能返回比左表中存在的更多的行，但它发生的都是一样的！
SQL Query follows:
SQL 查询如下：
SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID
FROM SUSP.Susp_Visits LEFT OUTER JOIN
DATA.Dim_Member ON SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum
Perhaps I have made a mistake in the syntax or my understanding of LEFT OUTER JOIN is incomplete, hopefully someone can explain how this could be occurring?
也许我在语法上犯了一个错误，或者我对 LEFT OUTER JOIN 的理解不完整，希望有人能解释一下这是怎么发生的？
Postscript
后记
Thanks for the great answers, my understanding of LEFT OUTER JOINS is now much better, could anyone however suggest a way this query could be modified so that I only get as many records returned as exist in the left table?
感谢您提供出色的答案，我现在对 LEFT OUTER JOINS 的理解要好得多，但是有人可以建议修改此查询的方法，以便我只返回与左表中存在的记录一样多的记录吗？
This query is purely to generate a report and the duplicate matches simply confuse matters.
此查询纯粹是为了生成报告，重复的匹配只会混淆问题。
/Postscript
/后记
回答by Robin Day
The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.
LEFT OUTER JOIN 将返回 LEFT 表中的所有记录，并在可能的情况下与 RIGHT 表连接。
If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN.
如果有匹配，它仍然会返回所有匹配的行，因此，与 RIGHT 中的两行匹配的 LEFT 中的一行将返回为两个 ROWS，就像 INNER JOIN 一样。
EDIT: In response to your edit, I've just had a further look at your query and it looks like you are only returning data from the LEFT table. Therefore, if you only want data from the LEFT table, and you only want one row returned for each row in the LEFT table, then you have no need to perform a JOIN at all and can just do a SELECT directly from the LEFT table.
编辑：为了回应您的编辑，我刚刚进一步查看了您的查询，看起来您只是从 LEFT 表中返回数据。因此，如果您只需要 LEFT 表中的数据，并且您只希望为 LEFT 表中的每一行返回一行，那么您根本不需要执行 JOIN，只需直接从 LEFT 表中执行 SELECT。
回答by Andrew Lewis
Table1 Table2
_______ _________
1 2
2 2
3 5
4 6
SELECT Table1.Id, Table2.Id FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Id=Table2.Id
Results:
结果：
1,null
2,2
2,2
3,null
4,null
回答by HLGEM
It isn't impossible. The number of records in the left table is the minimum number of records it will return. If the right table has two records that match to one record in the left table, it will return two records.
这不是不可能的。左表中的记录数是它将返回的最小记录数。如果右表有两条记录与左表中的一条记录匹配，则返回两条记录。
回答by Chris Cameron-Mills
In response to your postscript, that depends on what you would like.
回应你的附言，这取决于你想要什么。
You are getting (possible) multiple rows for each row in your left table because there are multiple matches for the join condition. If you want your total results to have the same number of rows as there is in the left part of the query you need to make sure your join conditions cause a 1-to-1 match.
您为左表中的每一行获取（可能）多行，因为连接条件有多个匹配项。如果您希望您的总结果具有与查询左侧部分相同的行数，您需要确保您的连接条件导致 1 比 1 匹配。
Alternatively, depending on what you actually want you can use aggregate functions (if for example you just want a string from the right part you could generate a column that is a comma delimited string of the right side results for that left row.
或者，根据您的实际需要，您可以使用聚合函数（例如，如果您只想要来自右侧的字符串，您可以生成一个列，该列是该左行右侧结果的逗号分隔字符串。
If you are only looking at 1 or 2 columns from the outer join you might consider using a scalar subquery since you will be guaranteed 1 result.
如果您只查看外部连接中的 1 或 2 列，您可能会考虑使用标量子查询，因为您将得到 1 个结果。
回答by Alex Martelli
Each record from the left table will be returned as many times as there are matching records on the right table -- at least 1, but could easily be more than 1.
左表中的每条记录将返回与右表中匹配记录一样多的次数——至少为 1，但很容易超过 1。
回答by topchef
LEFT OUTER JOIN just like INNER JOIN (normal join) will return as many results for each row in left table as many matches it finds in the right table. Hence you can have a lot of results - up to N x M, where N is number of rows in left table and M is number of rows in right table.
LEFT OUTER JOIN 就像 INNER JOIN（普通连接）一样，将为左表中的每一行返回与它在右表中找到的匹配项一样多的结果。因此，您可以有很多结果 - 最多 N x M，其中 N 是左表中的行数，M 是右表中的行数。
It's the minimum number of results is always guaranteed in LEFT OUTER JOIN to be at least N.
在 LEFT OUTER JOIN 中始终保证最少的结果数至少为 N。
回答by Ken Burkhardt
Could it be a one to many relationship between the left and right tables?
左右表之间可能是一对多的关系吗？
回答by A-K
If you need just any one row from the right side
如果您只需要右侧的任何一行
SELECT SuspReason, SiteID FROM(
SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID, ROW_NUMBER()
OVER(PARTITION BY SUSP.Susp_Visits.SiteID) AS rn
FROM SUSP.Susp_Visits
LEFT OUTER JOIN DATA.Dim_Member ON SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum
) AS t
WHERE rn=1
or just
要不就
SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID
FROM SUSP.Susp_Visits WHERE EXISTS(
SELECT DATA.Dim_Member WHERE SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum
)
回答by Serge
Pay attention if you have a where clause on the "right side' table of a query containing a left outer join... In case you have no record on the right side satisfying the where clause, then the corresponding record of the 'left side' table will not appear in the result of your query....
注意如果在包含左外连接的查询的“右侧”表上有 where 子句...如果右侧没有满足 where 子句的记录，则“左侧”对应的记录' 表不会出现在您的查询结果中....
回答by bdukes
It seems as though there are multiple rows in the DATA.Dim_Member table per SUSP.Susp_Visits row.
似乎每个 SUSP.Susp_Visits 行的 DATA.Dim_Member 表中有多行。
