如何与 oracle 中可能为空的值进行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12378020/
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
How can I compare against a possibly null value in oracle?
提问by dee
I see a number of variations on this question but they all seem to talk about performance and indexes when asking about comparing null values.
我看到有关此问题的许多变体，但在询问比较空值时，它们似乎都在谈论性能和索引。
My issue is that I have a nested SELECT that can return null that I am comparing against based on a date parameter.
我的问题是我有一个嵌套的 SELECT 可以返回 null，我正在根据日期参数进行比较。
SELECT a.*
FROM Table_One a,
Table_Two b
WHERE a.Fieldc IN (SELECT CompareValue from Table_Three cv WHERE inDate between cv.Date_ and SYSDATE)
AND a.Fielda = b.Fieldb(+)
it seems like when the nested select here returns null then the left join messes up.
似乎当这里的嵌套选择返回 null 时，左连接就搞砸了。
I'll be happy to include some fake data if it's needed but maybe I'm just missing something. What I need to have occur is that the Fieldc comparison only occurs within that date specification.
如果需要，我很乐意包含一些虚假数据，但也许我只是遗漏了一些东西。我需要发生的是 Fieldc 比较只发生在该日期规范内。
Thanks for any insight. Thanks.
感谢您的任何见解。谢谢。
Further information:
更多信息：
Table_One Data: all fields are varchar2
Table_One Data：所有字段都是varchar2
Fielda | Fieldb | Fieldc|
'aVal' | 'bVal' | 'cVal'|
'dVal' | 'eVal | 'fVal'|
'dVal' | 'fVal | 'eVal'|
Table_Two Data: all fields are varchar2
Table_Two 数据：所有字段都是varchar2
Fielda | Fieldb | Fieldc|
'aVal' | 'bVal' | 'cVal'|
'dVal' | 'fVal | 'gVal'|
'dVal' | 'fVal | 'cVal'|
Table_Three Data: CompareValue is varchar2, date_ is date
Table_Three 数据：CompareValue 为 varchar2，date_ 为日期
CompareValue | date_ |
'fval' | 2012-09-10 |
So if the parameter were today the select returns 'fval' and we get left join correctly. however, when the date parameter is before '2012-09-10' then the nested select is comparing a null and the left join does not happen properly. This is a bit of a simplified version of the original.
因此，如果参数是今天，则选择返回 'fval' 并且我们正确地获得左连接。但是，当日期参数在 '2012-09-10' 之前时，嵌套选择正在比较 null 并且左连接不会正确发生。这是原始版本的一个简化版本。
Thanks
谢谢
回答by Randy
use NVL(inDate, sysdate+1)to not match when null
用于NVL(inDate, sysdate+1)在 null 时不匹配
回答by winkbrace
I assume you are selecting from table a on the condition that a.Fieldc is considered "active", because in table_three it's inDate lies between some start date and sysdate.
我假设您在 a.Fieldc 被视为“活动”的条件下从表 a 中进行选择，因为在 table_three 中，它的 inDate 位于某个开始日期和 sysdate 之间。
Therefore I don't think you want to always have a result. Instead I'd advise you to handle an empty result in your code.
因此，我认为您不想总是有结果。相反，我建议您在代码中处理空结果。
I'm afraid I cannot give a more precise answer, because you haven't shared any more information about your case.
恐怕我无法给出更准确的答案，因为您还没有分享有关您案件的更多信息。
回答by AnBisw
You need to understand the difference between No recordsand Nullvalues. When you say - "when the date parameter is before '2012-09-10' then the nested select is comparing a null and the left join does not happen properly", this means the SELECTreturns a row but the row value is NULL(this is not same as No records). In this case, doing an NVLon the NULLcolumn value and replacing it with a fake string (as you said) would work like-
您需要了解No records和Null值之间的区别。当您说 - “当日期参数在 '2012-09-10' 之前，则嵌套选择正在比较 null 并且左连接没有正确发生”，这意味着SELECT返回一行但行值是NULL（这是与无记录不同）。在这种情况下，NVL对NULL列值执行操作并将其替换为假字符串（如您所说）会像-
SELECT NVL(CompareValue,'dummy')
from Table_Three cv WHERE inDate between cv.Date_ and SYSDATE
The above would return dummyif CompareValueis NULLbut not if the query returns NO RECORDS at all.
如果查询根本不返回任何记录，则上面将返回dummyif CompareValueisNULL但不返回。
However, looking at your data, I do not see a possibility of a NULL value which leads me to believe that the subquery will actually return NO RECORDS (not same as NULL) if inDateis before 2012-09-10. In this case your query should be perfectly fine (unless there is something else going wrong somewhere else).
但是，查看您的数据，我看不到 NULL 值的可能性，这使我相信NULL如果inDate在 2012-09-10 之前，子查询实际上将返回 NO RECORDS（与 不同）。在这种情况下，您的查询应该完全没问题（除非其他地方出了问题）。
