LEFT OUTER JOIN Puzzle: Why More Results Than Left Table Records?

LEFT OUTER JOIN Problem: The number of records exceeds the left table?

While it is generally accepted that a LEFT OUTER JOIN always returns all records from the left table, one user was confused when the query returned more results than expected.

The SQL query in question:

<code class="language-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</code>

Uncover the truth:

The misconception is the assumption that a LEFT OUTER JOIN will only return NULL values ​​for unmatched rows in the left table. In fact, it returns all matching rows, even if multiple rows from the right table match a row from the left table.

Example:

Consider the following form:

**Susp_Visits**		**Dim_Member**
MemID: 1		MembershipNum: 1
MemID: 1		MembershipNum: 2
MemID: 2		MembershipNum: NULL

A LEFT OUTER JOIN based on MemID will produce the following results:

**SuspReason**	**SiteID**	**MembershipNum**
1	1	1
1	1	2
2	2	NULL

As you can see, the row with Susp_Visits.SuspReason of 1 is duplicated because it matches two rows in Dim_Member.

Alternative:

If the desired result is to retrieve only the unique row from the left table, no LEFT OUTER JOIN is required. Just do a simple SELECT from the table on the left:

<code class="language-sql">SELECT     
    SuspReason, SiteID
FROM
    SUSP.Susp_Visits</code>

The above is the detailed content of LEFT OUTER JOIN Puzzle: Why More Results Than Left Table Records?. For more information, please follow other related articles on the PHP Chinese website!