LEFT OUTER JOIN 如何返回比左表中存在的记录更多的记录? [英] How can a LEFT OUTER JOIN return more records than exist in the left table?

问题描述

我有一个非常基本的 LEFT OUTER JOIN 来返回左表中的所有结果和一个更大表中的一些附加信息.左表包含 4935 条记录，但当我将其左外连接到另一个表时，记录数明显更大.

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 将返回左表中的所有记录，右表中的匹配记录和任何无法匹配的行的空值，这是绝对的福音，因此这是我的理解应该不可能返回比左表中存在的更多的行，但它发生的都是一样的！

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!

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

也许我在语法上犯了一个错误，或者我对 LEFT OUTER JOIN 的理解不完整，希望有人能解释一下这是怎么发生的?

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 JOINS 的理解要好得多，但是有人可以建议修改此查询的方法，以便我只能返回与左表中存在的记录一样多的记录吗?

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?

此查询纯粹是为了生成报告，重复的匹配只会混淆问题.

This query is purely to generate a report and the duplicate matches simply confuse matters.

/后记

推荐答案

LEFT OUTER JOIN 将在可能的情况下返回 LEFT 表中与 RIGHT 表连接的所有记录.

The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.

如果有匹配，它仍然会返回所有匹配的行，因此，匹配 RIGHT 中两行的 LEFT 中的一行将返回为两个 ROWS，就像一个 INNER JOIN.

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.

为了回应您的编辑，我刚刚进一步查看了您的查询，看起来您只是从 LEFT 表中返回数据.因此，如果您只需要 LEFT 表中的数据，并且您只希望为 LEFT 表中的每一行返回一行，那么您根本不需要执行 JOIN，只需直接从 LEFT 表中执行 SELECT.

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 OUTER JOIN 如何返回比左表中存在的记录更多的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！