Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 812
Jay Wilde Avatar asked Oct 19 '22 06:10

Jay Wilde


People also ask

WHY IS LEFT join giving me more rows?

So, if your secondary tables have more than one row for the key you're joining with, then the result of the join will be multiple rows, resulting in more rows than the left table.

Can left outer join causes duplicates?

Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).

How many records does LEFT join return?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

WHAT IF LEFT join has multiple matches?

LEFT JOINs can return multiple matches on a single key value, depending on your requirements this may or may not be desirable. In Oracle, you can use the analytical function ROW_NUMBER to choose a single value when multiple matches on a key occur.


1 Answers

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.

like image 245
Robin Day Avatar answered Oct 26 '22 14:10

Robin Day