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
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.
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!
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?).