SQL“加入"在空值上 [英] SQL "Join" on null values

问题描述

由于无法控制的原因，我需要连接两个表，并且需要空值来匹配.我能想到的最好的选择是吐出一个UUID并将其用作我的比较值，但这看起来很难看

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

我该如何做得更好?如果重要的话，这是在Oracle上进行的，并且上下文是一个应用程序，在该应用程序中必须将一批用户上载的数据与一批现有数据进行比较，以查看是否有任何行匹配.回想起来，我们应该阻止任何一个数据集中的联接列包含空值，但是我们没有，现在必须忍受它.

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.

为清楚起见，我并非只与空有关.如果这些列不为空，则希望它们与它们的实际值匹配.

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.

推荐答案

也许可以，但是我从未真正尝试过:

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)

这篇关于SQL“加入"在空值上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！