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

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

User · Answer

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

User · Answer

If you need just any one row from the right side  SELECT SuspReason  SiteID FROM      SELECT SUSP Susp Visits SuspReason  SUSP Susp Visits SiteID  ROW NUMBER       OVER PARTITION BY SUSP Susp Visits SiteID  AS rn     FROM SUSP Susp Visits     LEFT OUTER JOIN DATA Dim Member ON SUSP Susp Visits MemID   DATA Dim Member MembershipNum   AS t WHERE rn 1   or just  SELECT SUSP Susp Visits SuspReason  SUSP Susp Visits SiteID FROM SUSP Susp Visits WHERE EXISTS      SELECT DATA Dim Member WHERE SUSP Susp Visits MemID   DATA Dim Member MembershipNum

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It seems as though there are multiple rows in the DATA Dim Member table per SUSP Susp Visits row

User · Answer

Table1                Table2                                 1                      2 2                      2 3                      5 4                      6  SELECT Table1 Id  Table2 Id FROM Table1 LEFT OUTER JOIN Table2 ON Table1 Id Table2 Id   Results   1 null 2 2 2 2 3 null 4 null

User · Answer

In response to your postscript  that depends on what you would like   You are getting  possible  multiple rows for each row in your left table because there are multiple matches for the join condition  If you want your total results to have the same number of rows as there is in the left part of the query you need to make sure your join conditions cause a 1-to-1 match   Alternatively  depending on what you actually want you can use aggregate functions  if for example you just want a string from the right part you could generate a column that is a comma delimited string of the right side results for that left row   If you are only looking at 1 or 2 columns from the outer join you might consider using a scalar subquery since you will be guaranteed 1 result

User · Answer

Pay attention if you have a where clause on the  right side  table of a query containing a left outer join     In case you have no record on the right side satisfying the where clause  then the corresponding record of the  left side  table will not appear in the result of your query

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql