question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Inner join vs outer join

I am investigating some code that was written some time ago, the script has a left outer join but one of the fields that is in the where clause comes from the outer table therefore making the join irrelevant. I would have written this as an inner join, can anyone please advise me as to why this could have been written as an outer join. Select a.col1, a.col2, isnull(b.col3,0) from tablea a left outer join tableb b on a.colz = b.colz where isnull(b.col3,0) = 4
joins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
My first thought would be missing knowledge/understanding on the part of the developer that wrote it. Maybe they didn't know about inner joins, maybe they had been bitten by an inner join filtering out results that they actually wanted and defaulted their coding to use outer joins from there on. I would use an inner join too.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you, that reassures me that I do know a little about what I'm doing!!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
I have seen this kind of code before, generally when the filter has been added at a later stage, and either the dev has been lucky that it never actually mattered, or they haven't realised that the filter now negates the outer join. But as to why it could have been written like this from the start - I've no idea!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnStaffordDBA avatar image
JohnStaffordDBA answered
Basically, the query is forcing the outer join to behave like an inner join, which I have seen in Crystal Reports when tables are joined and then filters added later. Since the query needs to test a value from tableb, I'd say you may as well change the join to an inner join (which will hopefully improve its perfromance as well?).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.