如何在sql中同时测试null和not null? [英] How can I test for null and not null at the same time in sql?

问题描述

我有这个sql:

select * from employees
where
lastname like '%smith%' and firstname like '%bob%' and middle like '%%'

问题在于名称的值，尤其是中间名，可以为null.因此，上面的语句将找到:

The problem is the values of the name, particularly the middle name, can be null. So the statement above will find:

  smith, bob mike

但找不到

  smith, bob

因为鲍勃的中间名为空(他没有中间名.)

because Bob's middle name is null (he has no middle name.)

我有这个可行的方法，但我认为有更好的方法，并希望得到输入:

I have this which works but I thought there was a better way and wanted input:

    select * from employees
    where
    lastname like '%smith%' 
    and firstname like '%bob%' 
    and (middle like '%%' or middle is null)

中间并不总是空白.有时它会有一个值，所以我必须保留中间名的测试.它可以填充或为空.

middle is not always blank. Sometimes it will have a value so I have to keep the test for middle name in. It could be populated or null.

谢谢.如果重要的话，我正在使用Oracle 11g.

Thanks. I am using Oracle 11g if that matters.

推荐答案

您可以使用nvl:

select * from employees
where
lastname like '%smith%' and firstname like '%bob%' and nvl(middle,'') like '%%'

但这与根本不检查中间名相同，因为'%%'将匹配null以外的任何内容.

But this is just the same as not checking the middle name at all as like '%%' will match anything other than null.

这篇关于如何在sql中同时测试null和not null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！