SQL 不等于 & 为空

SQL not equals & null

提问by Marcus Leon

We'd like to write this query:

我们想写这个查询：

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

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?

我们希望查询包含 col1 为空的行（并且 col2 = 'something'）。目前，查询不会对 col1 为空的行执行此操作。下面的查询是最好和最快的方法吗？

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

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.

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

---

---

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

更新：回复：使用 NVL：我在另一篇文章中读到，从性能角度来看，这不是一个很好的选择。

回答by Thilo

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

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

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

这是对 SQL 标准的公然无视，但是你去......

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".

除此之外，您不能使用“正常”运算符与 NULL（或非 NULL）进行比较：“col1 = null”不起作用，“col1 = ''”不起作用，“col1 != null”不起作用，您必须使用“为空”。

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

因此，不，您不能以任何其他方式进行这项工作，然后“col 1 为空”或对其进行一些变体（例如使用 nvl）。

回答by Sam

I think that the solution that you posted is one of best options.

我认为您发布的解决方案是最佳选择之一。

Regarding to performance, in my opinion it is not a big difference in this case, if the clause already have a != comparison usually the optimizer won't use an index in that column, because the selectivity is not enough, so the more discriminating filter will be the other side of the "and" condition.

关于性能，我认为在这种情况下差别不大，如果子句已经有 != 比较，通常优化器不会在该列中使用索引，因为选择性不够，所以更具辨别力filter 将是“and”条件的另一边。

If you ask me, I won't use an empty string as a null, but may be is just a personal preference.

如果你问我，我不会使用空字符串作为 null，但可能只是个人喜好。

回答by JettingSpike

While not the most readable - Oracle has an LNNVL Functionthat is essentially the not() function, but inverts the behavior for nulls. Meaning that comparing anything with null inside of lnnvl will return true (I don't know what performance implications this may have).

虽然不是最易读的——Oracle 有一个LNNVL 函数，它本质上是 not() 函数，但反转了空值的行为。这意味着将 lnnvl 中的任何内容与 null 进行比较将返回 true（我不知道这可能对性能有什么影响）。

To do what you want in a single statement:

要在单个语句中执行您想要的操作：

select * from table where lnnvl(col1 = 'blah') and col2 = 'something'

Note that this will only work for comparing a nullable value against a value you can be assured is non-nullable. Otherwise you'll need to do as Thilo suggests - use an operator similar to

请注意，这仅适用于将可空值与您可以确保不可为空的值进行比较。否则，您将需要按照 Thilo 的建议进行操作 - 使用类似于

lnnvl(nvl(col1, -1) = nvl(col2, -1))

回答by Jeffrey Melloy

It depends on your data, but most optimizers are going to look at col2 before col1, since = is an easier index than !=.

这取决于您的数据，但大多数优化器会在 col1 之前查看 col2，因为 = 比 != 更容易索引。

Otherwise, there are various ways you can speed this query up. It's probably best to do (col1 != 'blah' or col1 is null), but some database allow you to index a function. So you can index coalesce(col1, 0) and get good performance.

否则，您可以通过多种方式加快此查询的速度。最好这样做（col1 != 'blah' 或 col1 为空），但某些数据库允许您索引函数。所以你可以索引 coalesce(col1, 0) 并获得良好的性能。

Really it depends on you data and your table.

这真的取决于你的数据和你的表。

回答by Jeffrey Kemp

If you want to speed up this sort of query, and you're on Oracle 10g or later, use a function-based index to turn those NULLs into values:

如果您想加快此类查询的速度，并且您使用的是 Oracle 10g 或更高版本，请使用基于函数的索引将这些 NULL 转换为值：

CREATE INDEX query_specific_index ON table (col2, NVL(col1,'***NULL***'));

select * from table 
where NVL(col1,'***NULL***') != 'blah' and col2 = 'something';

The database will quite likely use the index in this scenario (of course, subject to the decision of the CBO, affected by row counts and the accuracy of the statistics). The query MUST use the exact expression given in the index - in this case, "NVL(col1,'***NULL***')"

在这种情况下，数据库很可能会使用索引（当然，取决于 CBO 的决定，受行数和统计数据准确性的影响）。查询必须使用索引中给出的确切表达式 - 在这种情况下，“ NVL(col1,'***NULL***')”

Of course, pick a value for '***NULL***'that will not conflict with any data in col1!

当然，选择一个'***NULL***'不会与 col1 中的任何数据冲突的值！

回答by Alex

What about this option. I think it may work if your value is never null.

这个选项怎么样。我认为如果您的值从不为空，它可能会起作用。

where not (value = column)

which would result in following truth table for evaluation for the where clause

这将导致以下真值表用于评估 where 子句

                    col1
              | 'bla' |  null |
              -----------------
      | 'bla' |   F   |   T   |
value -------------------------
      |  null |   T   |  *T   | 

*this is the only one that's "wrong" but that's ok since our value is never null

*这是唯一一个“错误”的，但没关系，因为我们的值从不为空

Update

更新

Ok, I just tried out my idea and it failed. I'll leave the answer here to save time of others trying the same thing. Here are my results:

好吧，我刚刚尝试了我的想法，但失败了。我会在这里留下答案，以节省其他人尝试同样事情的时间。这是我的结果：

select 'x', 'x' from dual where not ('x' = 'x');
0 rows
select 'x', 'y' from dual where not ('x' = 'y');
1 row
select 'x', 'null' from dual where not ('x' = null);
0 rows
select 'null', 'null' from dual where not (null = null);
0 rows

Update 2

更新 2

This solution works if your value is never null (matches the truth table above)

如果您的值从不为空（与上面的真值表匹配），则此解决方案有效

where ('blah' != col1 or col1 is null)

tests here:

在这里测试：

select 'x', 'x' from dual where ('x' != 'x' or 'x' is null);
0 rows
select 'x', 'y' from dual where ('x' != 'y' or 'y' is null);
1 row
select 'x', 'null' from dual where ('x' != null or null is null);
1 row
select 'null', 'null' from dual where (null != null or null is null);
1 row

回答by Josh Bush

In oracle use the nvl function

在 oracle 中使用 nvl 函数

select * from table where nvl(col1,'notblah') <> 'blah'

回答by MrTelly

For Oracle

甲骨文

select * from table where nvl(col1, 'value') != 'blah' and col2 = 'something'

For SqlServer

对于 SqlServer

select * from table where IsNull(col1, '') <> 'blah' and col2 = 'something'

回答by Suroot

I think that your increase would be minimal in changing NULL values to "" strings. However if 'blah' is not null, then it should include NULL values.

我认为将 NULL 值更改为 "" 字符串的增加幅度很小。但是，如果 'blah' 不为 null，则它应该包含 NULL 值。

EDIT:I guess I'm surprised why I got voted down here. If 'blah' if not null or an empty string, then it should never matter as you are already checking if COL1 is not equal to 'blah' which is NOT a NULL or an empty string.

编辑：我想我很惊讶为什么我在这里被否决了。如果 'blah' 不是 null 或空字符串，那么它应该无关紧要，因为您已经在检查 COL1 是否不等于 'blah' ，后者不是 NULL 或空字符串。