By Jay Wilde


2009-05-27 15:30:11 8 Comments

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

11 comments

@HLGEM 2009-05-27 15:33:25

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.

@A-K 2009-05-27 15:57:35

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
)

@A-K 2009-05-27 16:22:23

Because you did not provide DDL and DML, I did not test. Anyway I think that EXISTS is what you want. Try this: SELECT SuspReason, SiteID FROM( SELECT SUSP.Susp_Visits.SuspReason, SUSP.Susp_Visits.SiteID, ROW_NUMBER() OVER(PARTITION BY SUSP.Susp_Visits.SiteID ORDER 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

@Serge 2014-09-06 09:58:03

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

@Mik 2017-03-21 10:30:11

You should then add the condition to the ON clause of the corresponding LEFT OUTER JOIN.

@Robin Day 2009-05-27 15:32:47

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.

@Jay Wilde 2018-09-06 17:43:54

The reason for joining to the right table was so I only got records from the left where there was at least one record in the right table but thank you so much for the explanation.

@Chris Cameron-Mills 2009-05-27 15:57:41

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.

@karns 2015-03-03 20:01:06

This is a good answer since it offered suggestions on how to return just rows from left table.

@Andrew Lewis 2009-05-27 15:38:26

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

@topchef 2009-05-27 15:34:33

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.

@MrKiller21 2013-09-26 13:15:05

I started to think when the number of rows is equal N x M and the only real situation which comes to my mind is when N or M equals 1. Do you agree?

@topchef 2013-09-26 16:12:54

No, I don't. You shouldn't think of join condition as key equality join only. It can be arbitrary condition, e.g. date ranges, inequalities, etc. Two extreme cases: (a) N rows have not a single match among M rows, then left outer join results in N rows matched up with NULLs. (b) every of N rows matches all of M rows, then result is N x M rows set.

@MrKiller21 2013-09-27 11:24:01

You're right, I was thinking about joins only in terms of key equality. I like your example from "case b". I believe that "every of N rows matches all of M rows" is a general recipe for when N x M rows are returned, which is rather impossible to visualize when thinking about key equality only.

@Manu 2009-05-27 15:33:38

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.

@Alex Martelli 2009-05-27 15:33:25

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.

@bdukes 2009-05-27 15:32:56

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

@Ken Burkhardt 2009-05-27 15:32:55

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

Related Questions

Sponsored Content

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

27 Answered Questions

3 Answered Questions

[SOLVED] Replace Default Null Values Returned From Left Outer Join

22 Answered Questions

[SOLVED] How can I list all foreign keys referencing a given table in SQL Server?

  • 2009-01-27 12:17:59
  • chillitom
  • 539679 View
  • 648 Score
  • 22 Answer
  • Tags:   sql sql-server tsql

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

7 Answered Questions

13 Answered Questions

[SOLVED] How to drop a table if it exists in SQL Server?

  • 2011-10-25 09:05:46
  • tmaster
  • 1030278 View
  • 637 Score
  • 13 Answer
  • Tags:   sql sql-server

2 Answered Questions

3 Answered Questions

[SOLVED] LEFT OUTER JOINs not acting as expected

  • 2014-02-20 21:58:50
  • ChadD
  • 170 View
  • 1 Score
  • 3 Answer
  • Tags:   sql sql-server

Sponsored Content