How can a LEFT OUTER JOIN return more records than exist in the left table?

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.

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 query is as follows:

    SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID
    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?


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

    If there are matches, though, it will still return all rows that match. Therefore, one row in the LEFT table that matches two rows in the RIGHT table will return as two rows, just like an INNER JOIN.

    Looking at the edited query for this specific question, it appears 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.