Left Outer Join

Question

Post reply

Left Outer Join

forum member

Ten Centuries

Points: 1290
More actions
January 12, 2010 at 7:52 pm

#208945

Hi,
I am using left outer join on two tables,i allways get the null values from second table even though match exists
SELECT a.IDNo,b.IDNO,a.Name,b.Name
from table1 a Left Outer Join table2 b
on
a.id1=b.id1
and a.lid1=b.lid2
and a.lid3=b.lid3
Please suggest on how to resolve this
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Seems to work fine for me:

DECLARE @Table1

TABLE

(

idno INT NULL,

id1 INT NULL,

lid1 INT NULL,

lid3 INT NULL,

name VARCHAR(20) NULL

);

DECLARE @Table2

TABLE

(

idno INT NULL,

id1 INT NULL,

lid1 INT NULL,

lid2 INT NULL,

lid3 INT NULL,

name VARCHAR(20) NULL

);

INSERT @Table1

(idno, id1, lid1, lid3, name)

VALUES (0, 1, 2, 3, 'Table 1');

INSERT @Table2

(idno, id1, lid1, lid2, lid3, name)

VALUES (0, 1, 0, 2, 3, 'Table 2');

SELECT a.IDNo,

b.IDNO,

a.Name,

b.Name

FROM @Table1 a

LEFT

OUTER

JOIN @Table2 b

ON (

a.id1 = b.id1

AND a.lid1 = b.lid2

AND a.lid3 = b.lid3

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

If Paul's Script does not work for you, please post sample data and table structures so we can better match the problem with an answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 3

CirquedeSQLeil (1/12/2010)
If Paul's Script does not work for you, please post sample data and table structures so we can better match the problem with an answer.

Quite so. In so far as it is possible to post sarcastic SQL code, that's what I was aiming for with my original post...;-)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

forum member Ten Centuries Points: 1290 More actions · Answer 4

Hi there,

The script provided works but in my case the table 2 is generated and contains data which is a result of other joins

so when i have a left outer join as above i am getting null resulted values from table2

but when i do a inner join there exists match and returns the rows

Please suggest on how to resolve this

My table structure are very big with large quantinty of data so couldn't make a post

Thanks

Paul White SSC Guru Points: 150467 More actions · Answer 5

forum member (1/13/2010)
My table structure are very big with large quantinty of data so couldn't make a post.

There is an error in your code.

Without seeing what you can see (even a simplified example!?) it's difficult to see how to help...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Paul White (1/13/2010)
forum member (1/13/2010)
My table structure are very big with large quantinty of data so couldn't make a post.
There is an error in your code.
Without seeing what you can see (even a simplified example!?) it's difficult to see how to help...?

Big table structure can be handled. As far as the data, just post some of the data that you know represents the issue (10 records maybe that are returned by both inner and left join).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 7

Paul, I giggled when I saw that 🙂

To the original poster, the logic in the where clause is a bit strange but then we don't know your data 🙂

a.id1=b.id1

and a.lid1=b.lid2

and a.lid3=b.lid3

lid1 = lid2 sounds like it could have potential for an issue. However If you are joining non int or decimal types such as char and nchar, it is quite possible there are leading or trailing spaces on one table that match. For instance sql server wont match ' bob' = 'bob ' in that join type.

Also if you have 'massive' amounts of data, when you are using a left join, all records from the left table are returned and only matching records contain non null data. If only a few records match and you try to eyeball across a million rows, you may not see that non null data. Whereas the inner join you could be returning the 10 records only that actually do match which makes it seem like its only working on the Inner join.

Anyways my 2 cents to help out without data or data structures 😀

Link to my blog http://notyelf.com/