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:
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?
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.