Outer Joins in SQL

I know this isn’t rocket science but I learnt a valuable lesson today and thought I should share it.

When doing an outer join, particularly a full outer join, make sure that the source tables are already filtered before the join. Sub-queries are your friend here.

For example,

select nvl(TF.FldName, MF.FldName) as FldName, TF.FldType as TFldType, MF.FldType as MFldType
 from T_Fields TF full outer join MetaDB_Fields MF on upper(TF.FldName) = upper(MF.FldName)
 where TF.TableID = 52 and MF.TableID = 54

doesn’t always give consistent results. If there are records where FldName matches where the TableID doesn’t match, you won’t get your expected null entries in the result set. The correct code would be as follows:

select nvl(TF.FldName, MF.FldName) as FldName, TF.FldType as TFldType, MF.FldType as MFldType
 from (select * from T_Fields where TableID = 52) TF
 full outer join (select * from MetaDB_Fields where TableID = 54) MF on upper(TF.FldName) = upper(MF.FldName)

Sadly, or maybe fortunately, I now have a lot more work to do to get the structure of these two databases to match!

Leave a Reply

Your email address will not be published. Required fields are marked *

*