SQL不等于&空值 [英] SQL not equals & null

问题描述

我们要编写此查询:

select * from table 
where col1 != 'blah' and col2 = 'something'

我们希望查询包含col1为null(并且col2 ='something')的行.当前，查询不会对col1为null的行执行此操作.下面的查询是最好，最快的方法吗?

We want the query to include rows where col1 is null (and col2 = 'something'). Currently the query won't do this for the rows where col1 is null. Is the below query the best and fastest way?

select * from table 
where (col1 != 'blah' or col1 is null) and col2 = 'something'

或者，如果需要，我们可以将所有col1空值更新为空字符串.这会是更好的方法吗?然后我们的第一个查询将起作用.

Alternatively, we could if needed update all the col1 null values to empty strings. Would this be a better approach? Then our first query would work.

更新:回复:使用NVL:我已经阅读了另一个 post ，从性能角度来看，这不是一个好选择.

Update: Re: using NVL: I've read on another post that this is not considered a great option from a performance perspective.

推荐答案

在Oracle中，空字符串和NULL之间没有区别.

In Oracle, there is no difference between an empty string and NULL.

这是对SQL标准的公然无视，但是您就可以了...

That is blatant disregard for the SQL standard, but there you go ...

除此之外，您无法使用普通"运算符与NULL(或非NULL)进行比较:"col1 = null"将不起作用，"col1 ="将不起作用，"col1！= null"将不起作用，您必须使用为空".

In addition to that, you cannot compare against NULL (or not NULL) with the "normal" operators: "col1 = null" will not work, "col1 = '' " will not work, "col1 != null" will not work, you have to use "is null".

因此，不，您只能通过"col 1为空"或对此进行某种修改(例如使用nvl)来进行其他操作.

So, no, you cannot make this work any other way then "col 1 is null" or some variation on that (such as using nvl).

这篇关于SQL不等于&空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！