Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL "Join" on null values

Tags:

sql

null

oracle

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.

like image 603
Dan Avatar asked Nov 18 '09 19:11

Dan


People also ask

Can you left join on NULL values?

Since it's not possible to join on NULL values in SQL Server like you might expect, we need to be creative to achieve the results we want. One option is to make our AccountType column NOT NULL and set some other default value. Another option is to create a new column that will act as a surrogate key to join on instead.

How do you handle NULL values in left join SQL?

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.


2 Answers

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) 
like image 138
Eric Petroelje Avatar answered Oct 11 '22 10:10

Eric Petroelje


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...)

like image 29
Cade Roux Avatar answered Oct 11 '22 12:10

Cade Roux