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

In oracle you can join null values using decode:

    SELECT * FROM T1 JOIN T2 ON DECODE(T1.SOMECOL, T2.SOMECOL, 1, 0) = 1

decode treats nulls as equal, so this works without "magic" numbers. The two columns must have the same data type.

It won't make the most readable code, but probably still better than t1.id = t2.id or (t1.id is null and t2.id is null)

Answer 3

@Sarath Avanavu

This one is not the best approach. If TA.COL1 keeps value 0 and TB.COL2 is NULL it will join those records, which is not correct.

SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);

Answer 4

I believe you could still could use nvl() for join:

SELECT *
FROM T1
JOIN T2 ON NVL(T2.COL1,-1)=NVL(T1.COL1,-1);

But you will need to add function based indexes on columns col1

CREATE INDEX IND_1 ON T1 (NVL(COL1,-1));
CREATE INDEX IND_2 ON T2 (NVL(COL1,-1));

Indexes should improve the speed of the join on NVL(..) significantly.

Answer 5

You could try using with the below query.

SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);

Answer 6

You can also use CASE to replace the null value in Subqueries, then JOIN the results:

SELECT T1.COL1 FROM
(
   (SELECT (CASE WHEN COL1 IS NULL THEN 'X' ELSE COL1 END) AS COL1 FROM TABLE1) T1
   JOIN
   (SELECT (CASE WHEN COL1 IS NULL THEN 'X' ELSE COL1 END) AS COL1 FROM TABLE2) T2
)
ON T1.COL1=T2.COL1

Answer 7

Simple, utilize COALESCE, which will return its first non-null parameter:

SELECT * FROM T1 JOIN T2 ON 
  COALESCE(T1.Field, 'magic string') = 
     COALESCE(T2.Field, 'magic string')

The only thing you will have to worry about is that 'magic string' cannot be among the legal values for the join field in either table.

Answer 8

For this sort of task Oracle internally uses an undocumented function sys_op_map_nonnull(), where your query would become:

SELECT *
FROM T1 JOIN T2 ON sys_op_map_nonnull(T1.SOMECOL) = sys_op_map_nonnull(T2.SOMECOL)

Undocumented, so be careful if you go this route.

Answer 9

Do you really want to be able to join the tables if a value is null? Can't you just exclude the possible null values in the join predicate? I find it hard to grok that rows in two tables can be related by a null value. If you have 100 nulls in table1.col_a and 100 nulls in table2.col_b, you're going to have 10000 rows returned just for the rows with null. It sounds incorrect.

However, you did say you need it. Can I suggest coalescing the null column into a smaller string as character comparisons are relatively expensive. Even better, coalesce the nulls into an integer if the data in the columns is going to be text. Then you have very quick 'comparisons' and you're unlikely to collide with existing data.

Answer 10

Isn't it the same as checking for presence of nulls in both columns?

SELECT * FROM T1, T2 WHERE T1.SOMECOL IS NULL and T2.SOMECOL IS NULL

or

SELECT * FROM T1 CROSS JOIN T2 WHERE T1.SOMECOL IS NULL and T2.SOMECOL IS NULL

Answer 11

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.

Answer 12

You can't do any better, but the JOIN you have will not do an actual "JOIN" in any way (there won't be any correlation between T1.SOMECOL and T2.SOMECOL other than they both have a NULL value for that column). Basically that means that you won't be able to use a JOIN on NULLs to see if rows match.

NULL is never equal to another NULL. How can something of unknown value be equal to something else of unknown value?

Answer 13

In SQL Server I have used:

WHERE (a.col = b.col OR COALESCE(a.col, b.col) IS NULL)

Obviously not efficient, because of the OR, but unless there's a reserved value you can map NULLs to on both sides without ambiguity or folding that's about the best you can do (and if there was, why was NULL even allowed in your design...)

Answer 14

Maybe this would work, but I've never actually tried it:

SELECT * 
FROM T1 JOIN T2 
ON T1.SOMECOL = T2.SOMECOL OR (T1.SOMECOL IS NULL AND T2.SOMECOL IS NULL)