How can a LEFT OUTER JOIN return more records than

Question

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 follows:

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?

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?

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

/Postscript

Answer 1

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.

Answer 2

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.

Answer 3

if multiple (x) rows in Dim_Member are associated with a single row in Susp_Visits, there will be x rows in the resul set.

Answer 4

Could it be a one to many relationship between the left and right tables?

Answer 5

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.

It's the minimum number of results is always guaranteed in LEFT OUTER JOIN to be at least N.

Answer 6

The 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 LEFT that matches two rows in RIGHT will return as two ROWS, just like an 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.