CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Jay Wilde
Jay Wilde

Reputation: 2914

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

Upvotes: 270

Views: 384466

Answers (14)

Robin Day
Robin Day

Reputation: 102538

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.

Upvotes: 276

Danylo Zherebetskyy
Danylo Zherebetskyy

Reputation: 1517

Simply, LEFT OUTER JOIN is the Cartesian product within each join key, along with the unmatched rows of the left table (i.e. for each key_x that has N records in table_L and M records in table_R the result will have N*M records if M>0, or N records if M=0)

Upvotes: 0

Andrew Lewis
Andrew Lewis

Reputation: 5256

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

Upvotes: 201

Binita Bharati
Binita Bharati

Reputation: 5908

The only way your query would return more number of rows than the left table ( which is SUSP.Susp_Visits in your case), is that the condition (SUSP.Susp_Visits.MemID = DATA.Dim_Member.MembershipNum) is matching multiple rows in the right table, which is DATA.Dim_Member. So, there are multiple rows in the DATA.Dim_Member where identical values are present for DATA.Dim_Member.MembershipNum. You can verify this by executing the below query:

select DATA.Dim_Member.MembershipNum, count(DATA.Dim_Member.MembershipNum) from DATA.Dim_Member group by DATA.Dim_Member.MembershipNum

Upvotes: 1

user716255
user716255

Reputation: 453

Since the left table contains 4935 records, I suspect you want your results to return 4935 records. Try this:

create table table1
(siteID int, 
SuspReason int)

create table table2
(siteID int, 
SuspReason int)

insert into table1(siteID, SuspReason) values 
(1, 678), 
(1, 186), 
(1, 723)
    
insert into table2(siteID, SuspReason) values 
(1, 678),
(1, 965)
   
select distinct t1.siteID, t1.SuspReason
from table1 t1 left join table2 t2 on t1.siteID = t2.siteID and t1.SuspReason = t2.SuspReason

union 

select distinct t2.siteID, t2.SuspReason 
from table1 t1 right join table2 t2 on t1.siteID = t2.siteID and t1.SuspReason = t2.SuspReason

Upvotes: 0

HLGEM
HLGEM

Reputation: 96630

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.

Upvotes: 65

A-K
A-K

Reputation: 17090

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
)

Upvotes: 10

Serge
Serge

Reputation: 59

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

Upvotes: 5

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

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.

Upvotes: 21

topchef
topchef

Reputation: 19813

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.

Upvotes: 8

Manu
Manu

Reputation: 29153

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.

Upvotes: 2

Alex Martelli
Alex Martelli

Reputation: 882481

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.

Upvotes: 15

bdukes
bdukes

Reputation: 156025

It seems as though there are multiple rows in the DATA.Dim_Member table per SUSP.Susp_Visits row.

Upvotes: 2

Ken Burkhardt
Ken Burkhardt

Reputation: 3558

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

Upvotes: 9

Related Questions