StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can a LEFT OUTER JOIN return more records than exist in the left table?
text
Body
copied!<p>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 significatnly larger.</p> <p>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!</p> <p>SQL Query follows:</p> <pre><code>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></pre> <p>Perhaps I have made a mistake in the syntax or my understanding of LEFT OUTER JOIN is incomplete, whatever the reason I'm going mad here, hopefully someone can explain how this could be occuring?</p> <p>Postscript</p> <p>Thanks for the grea 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? </p> <p>This query is purely to generate a report and the duplicate matches simply confuse matters.</p> <p>/Postscript</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload