SQL “Join” on null values

Question

For reasons beyond my control, I need to join two tables and I need null values to match. The best option I could think of was to spit out a UUID and use that as my comparison value but it seems ugly

SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') = 
   nvl(T2.SOMECOL,'f44087d5935dccbda23f71f3e9beb491')

How can I do better? This is on Oracle if it matters, and the context is an application in which a batch of user-uploaded data has to be compared to a batch of existing data to see if any rows match. In retrospect we should have prevented any of the join columns in either data set from containing nulls, but we didn't and now we have to live with it.

Edit: To be clear, I'm not only concerned with nulls. If the columns are not null I want them to match on their actual values.

Answer 1

Why not something like that :

SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'null') = nvl(T2.SOMECOL,'null')

I don't know why you are using the UUID. You could use any string not present in the columns, like the string "null", for example, for lower memory footprint. And the solution using nvl is much faster than the solution using or ... is null proposed by Eric Petroelje, for example.

Answer 2

Just throwing this out there -- is there a way you could coalesce those nulls into a known value, like an empty string? Not knowing much about how your table is laid out means that I can't be sure if you'll be losing meaning that way -- i.e. having an empty string represent "user refused to enter a phone number" and NULL being "we forgot to ask about it", or something like that?

Odds are it's not possible, I'm sure, but if it is, you'll have known values to compare and you can get a legit join that way.