如何处理 Oracle 中 NOT IN 和 NOT LIKE 语句中的空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11215542/
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 do I handle nulls in NOT IN and NOT LIKE statements in Oracle?
提问by dee
I have a long piece of PL/SQL which is working well except for one small part of the where clause.
我有很长一段 PL/SQL，除了 where 子句的一小部分外，它运行良好。
I'm expecting that one row should be returned but, because the fields the where clause checks are null, the row is not being returned despite meeting the criteria.
我期望应该返回一行，但是，因为 where 子句检查的字段为空，所以尽管满足条件，但不会返回该行。
I read a very good note here :
http://jonathanlewis.wordpress.com/2007/02/25/not-in/
我在这里读到了一篇很好的笔记：http:
//jonathanlewis.wordpress.com/2007/02/25/not-in/
It explains how Nulls affect NOT IN statements and I presume it's the same for NOT LIKE as well.
它解释了空值如何影响 NOT IN 语句，我认为 NOT LIKE 也是如此。
What I haven't been able to find is the comparable replacement I need. Do I need to somehow switch this into an IN and LIKE or provide something to return the row if NUll?
我无法找到的是我需要的类似替代品。我是否需要以某种方式将其转换为 IN 和 LIKE 或提供某些内容以在 NULL 时返回该行？
Here is a simplified version of my code.
这是我的代码的简化版本。
SELECT * FROM Temp_Table T
WHERE -- Other where constraints
AND (T.Col_One NOT LIKE 'AString'
OR T.Col_Two NOT IN ('BString','CString'))
In my situation the row would have nulls in both Col_One and Col_Two.
在我的情况下，该行在 Col_One 和 Col_Two 中都有空值。
Any help is greatly appreciated.
任何帮助是极大的赞赏。
Thanks.
谢谢。
回答by DCookie
Try this:
尝试这个：
AND (NVL(T.Col_One,'NuLl') NOT LIKE 'AString'
OR NVL(T.Col_Two,'NuLl') NOT IN ('BString','CString'))
回答by Branko Dimitrijevic
Something like this should work:
这样的事情应该工作：
T.Col_One IS NULL OR T.Col_One NOT LIKE 'AString'
OR T.Col_Two IS NULL OR T.Col_Two NOT IN ('BString','CString')
Please note that...
请注意...
T.Col_Two NOT IN ('BString','CString')
...is equivalent to...
……相当于……
T.Col_Two <> 'BString' AND T.Col_Two <> 'CString'
...which "collapses" to NULL in case T.Col_Two is NULL.
...在 T.Col_Two 为 NULL 的情况下“折叠”为 NULL。
回答by Richard Harrison
By convention NULLis not likeor equal to anything - the only valid test would be IS NULLwhich will return true.
按照惯例，NULL它不like等于或等于任何东西——唯一有效的测试是IS NULL哪个将返回 true。
回答by dcasanueva
Check the NVL function in Oracle documentation in : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
检查 Oracle 文档中的 NVL 函数：http: //docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
