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!