0

Is it possible to do a Full Join and have the NULL values that 'pour over' change to something else (like change all of these NULLs to 1s for instance)? The purpose would be to be able to distinguish between NULL values returned from the base table and NULL values returned from the outer join.

Thanks

TJE97
  • 67
  • 1
  • 7

2 Answers2

0

Say you have

A FULL OUTER JOIN B ON A.FK = B.ID

If the ID of B is null, you know that the row is all null because of the outer join. If the ID of B is not null, all nulls in B are actual values.

By symmetry the same holds from B to A.

Just keep the keys (A.FK and B.ID) in the SELECT and you'll have what you need to discern those two cases. Otherwise take a look at COALESCE(), it's what you ask for but it's not what you need.

pid
  • 11,472
  • 6
  • 34
  • 63
  • Can you give me an example with COALESCE for this problem? I don't care if the base table NULLs change (temporarily) or if the NULLs returned from the outer join are changed but one needs to change so that I can make a distinction between them – TJE97 Jul 09 '14 at 16:08
  • It's pretty standard across all implementations of SQL, so these examples should also apply to MySQL or Oracle: [COALESCE Examples](http://msdn.microsoft.com/en-us/library/ms190349.aspx) – pid Jul 10 '14 at 08:16
0

Maybe this will work. You compare the column you joined on. Check to see if one table is null and the other is not. If so then it came from the join. I'm sure there's scenarios I'm overlooking but it works in this little example.

SQL Fiddle Demo

SQLChao
  • 7,709
  • 1
  • 17
  • 32
  • I see what you mean but can you give me an example of where I would put this logic in the context of the join? thank you – TJE97 Jul 09 '14 at 16:09