空值上的 SQL“加入”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1758409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL "Join" on null values
提问by Dan
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
由于我无法控制的原因，我需要连接两个表，并且需要匹配空值。我能想到的最佳选择是吐出一个 UUID 并将其用作我的比较值，但它看起来很丑陋
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.
我怎样才能做得更好？如果重要的话，这是在 Oracle 上，上下文是一个应用程序，其中必须将一批用户上传的数据与一批现有数据进行比较，以查看是否有任何行匹配。回想起来，我们应该阻止任一数据集中的任何连接列包含空值，但我们没有，现在我们必须忍受它。
Edit: To be clear, I'm not onlyconcerned with nulls. If the columns are not null I want them to match on their actual values.
编辑：明确地说，我不仅关心空值。如果列不为空，我希望它们匹配它们的实际值。
回答by Eric Petroelje
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)
回答by Cade Roux
In SQL Server I have used:
在 SQL Server 中，我使用过：
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...)
显然效率不高，因为 OR，但除非有保留值，否则您可以将 NULL 映射到两侧而不会产生歧义或折叠，这是您可以做的最好的事情（如果有，为什么在您的设计中甚至允许 NULL .. .)
回答by Ken White
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.
你不能做得更好，但是你拥有的 JOIN 不会以任何方式进行实际的“加入”（T1.SOMECOL 和 T2.SOMECOL 之间不会有任何相关性，除了它们都有一个 NULL 值）柱子）。基本上这意味着您将无法在 NULL 上使用 JOIN 来查看行是否匹配。
NULL is never equal to another NULL. How can something of unknown value be equal to something else of unknown value?
NULL 永远不等于另一个 NULL。价值未知的东西怎么会等于其他价值未知的东西呢？
回答by David Aldridge
For this sort of task Oracle internally uses an undocumented function sys_op_map_nonnull(), where your query would become:
对于此类任务，Oracle 在内部使用未公开的函数 sys_op_map_nonnull()，您的查询将变为：
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.
无证，所以如果你走这条路要小心。
回答by Michael Hays
Simple, utilize COALESCE, which will return its first non-null parameter:
简单，利用COALESCE，它将返回其第一个非空参数：
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.
您唯一需要担心的是“魔术字符串”不能在任一表中的连接字段的合法值中。
回答by Josh Smeaton
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.
如果值为空，您真的希望能够加入表吗？你不能在连接谓词中排除可能的空值吗？我发现很难理解两个表中的行可以通过空值关联。如果 table1.col_a 中有 100 个空值，table2.col_b 中有 100 个空值，那么将只为具有空值的行返回 10000 行。这听起来不正确。
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.
但是，你确实说过你需要它。我可以建议将空列合并为一个较小的字符串，因为字符比较相对昂贵。更好的是，如果列中的数据将是文本，则将空值合并为一个整数。然后，您可以非常快速地进行“比较”，并且不太可能与现有数据发生冲突。
回答by Tamás Bárász
You can join null values using decode:
您可以使用decode以下方法连接空值：
SELECT * FROM T1 JOIN T2 ON DECODE(T1.SOMECOL, T2.SOMECOL, 1, 0) = 1
decodetreats nulls as equal, so this works without "magic" numbers. The two columns must have the same data type.
decode将空值视为相等，因此无需“魔术”数字即可工作。两列必须具有相同的数据类型。
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)
它不会产生最易读的代码，但可能仍然比 t1.id = t2.id or (t1.id is null and t2.id is null)
回答by Andrey Batyrenko
@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.
这不是最好的方法。如果 TA.COL1 保持值 0 并且 TB.COL2 为 NULL 它将加入这些记录，这是不正确的。
SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);
回答by Alexis Dufrenoy
Why not something like that :
为什么不是这样的：
SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'null') = nvl(T2.SOMECOL,'null')
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 nvlis much faster than the solution using or ... is nullproposed by Eric Petroelje, for example.
我不知道你为什么使用 UUID。您可以使用列中不存在的任何字符串，例如字符串“null”，以减少内存占用。例如，使用的解决方案nvl比使用or ... is nullEric Petroelje 提出的解决方案要快得多。
回答by Sarath Avanavu
You could try using with the below query.
您可以尝试使用以下查询。
SELECT *
FROM TABLEA TA
JOIN TABLEB TB ON NVL(TA.COL1,0)=NVL(TB.COL2,0);
