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屋！