oracle 如何不从不喜欢条件的地方排除 null？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15882329/
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 not to exclude null from where not like condition?
提问by Mowgli
In where clause for my query if I include condition in where clause such as
在查询的 where 子句中，如果我在 where 子句中包含条件，例如
where
T2.Comments not like '%ABC%'
and T2.Comments not like '%xyz%'
it also filters out blanks/null comments. I do not want it to filter out null or blanks.
它还过滤掉空白/空注释。我不希望它过滤掉空值或空白。
What is another way to filter out eg. ABCand xyzand also not exclude nulls/blanks?
过滤掉例如的另一种方法是什么？ABC并且xyz也不排除空值/空白？
rest of where caluse.. (after adding bottom two condition I do not get any nulls.
其余的 where caluse ..（添加底部两个条件后，我没有得到任何空值。
Where
T1.OUT_NO = T2.OUT_NO
AND T3.OUT_NO = T1.OUT_NO
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9)
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS'))
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND T2.Comments not like '%ABC%'
AND T2.Comments not like '%XYZ%'
回答by drquicksilver
just add NULLas a specific case. [edited to show how to generalise this approach to OPs more complex query]
只需添加NULL为特定情况。[编辑以展示如何将这种方法推广到 OP 更复杂的查询]
Where
T1.OUT_NO = T2.OUT_NO
AND T3.OUT_NO = T1.OUT_NO
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9)
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS'))
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND (T2.Comments IS NULL OR
(T2.Comments not like '%ABC%'
AND T2.Comments not like '%XYZ%')
)
回答by ypercube??
When you have a condition (column LIKE 'somecode')then all NULLvalues are excluded from the results. Only rows that have not null can result in the condition being true. So, I think you want:
当您有条件时，(column LIKE 'somecode')所有NULL值都将从结果中排除。只有不为空的行才能导致条件为真。所以，我认为你想要：
where
( ( T2.Comments not like '%ABC%'
and T2.Comments not like '%xyz%'
)
or T2.Comments IS NULL
)
ANDhas higher precedence than ORso the inner parentheses are not needed, only added for clarity (as @horse_with_no_name's comment). The outer ones are not needed either if you have only this condition.
AND具有更高的优先级，OR因此不需要内括号，只是为了清楚起见而添加（如@horse_with_no_name 的评论）。如果您只有这种情况，也不需要外部的。
In other DBMS, you would also need or T2.Comments = ''but not in Oracle, because NULLand the empty string are the same thing for char columns.
在其他 DBMS 中，您也需要or T2.Comments = ''但在 Oracle 中不需要，因为NULL对于 char 列，空字符串是相同的。
回答by F.Kokert
Putting it into a 'not like all' statement is making the intention clear and shortens the code for a better overview.
将其放入“不一样”的声明中可以明确意图并缩短代码以获得更好的概览。
and (T2.Comments not like all ('%ABC%','%xyz%') or T2.Comments is null)
