SQL "Join" on null values

user_207364 0
Viewed 88677

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.

null oracle sql
14 Answers

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)

user_47381080

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.

user_11956320

@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);
user_31602800
Related