为什么MYSQL IN关键字不考虑NULL值 [英] Why MYSQL IN keyword not considering NULL values

问题描述

我正在使用以下查询:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');

令人惊讶的是，该语句不包含错误值为NULL的行.我的意图是仅筛选错误值为超时"(或)连接错误"的行.我需要提供其他条件(或Error为NULL)以检索正确的结果.

Surprisingly, this statement doesnot include the rows having Error value as NULL.My intention is to filter only rows with Error value as 'Timeout' (or) 'Connection Error'. I need to give an additional condition( OR Error is NULL) to retrieve the correct result.

MYSQL为什么要用NULL值过滤掉结果? 我以为IN关键字会返回布尔结果(1/0)，现在我知道某些MYSQL关键字不会返回布尔值，它也可能会返回NULL....但是为什么将NULL视为特殊值呢?

Why is MYSQL filtering out results with NULL values? I thought that IN keyword would return a boolean result (1/0) and now i understand that some MYSQL keywords doesnt return boolean values,it might return NULL too....but Why is it treating NULL as special?

推荐答案

此:

Error not in ('Timeout','Connection Error');

在语义上等同于:

Error <> 'TimeOut' AND Error <> 'Connection Error'

有关空比较的规则也适用于IN.因此，如果Error的值为NULL，则数据库无法使表达式为真.

Rules about null comparison applies to IN too. So if the value of Error is NULL, the database can't make the expression true.

要解决此问题，您可以执行以下操作:

To fix, you could do this:

COALESCE(Error,'') not in ('Timeout','Connection Error');

或更妙的是:

Error IS NULL OR Error not in ('Timeout','Connection Error');

或更妙的是:

 CASE WHEN Error IS NULL THEN 1
 ELSE Error not in ('Timeout','Connection Error') THEN 1
 END = 1

OR不会短路，CASE可以使您的查询短路

OR doesn't short-circuit, CASE can somehow short-circuit your query

也许一个具体的例子可以说明为什么NULL NOT IN expression什么都不返回:

Perhaps a concrete example could illustrate why NULL NOT IN expression returns nothing:

提供此数据: http://www.sqlfiddle.com/#!2/0d5da/11

create table tbl
(
  msg varchar(100) null,
  description varchar(100) not null
  );


insert into tbl values
('hi', 'greet'),
(null, 'nothing');

然后您执行以下表达式:

And you do this expression:

select 'hulk' as x, msg, description 
from tbl where msg not in ('bruce','banner');

那只会输出"hi".

NOT IN的翻译为:

The NOT IN is translated as:

select 'hulk' as x, msg, description 
from tbl where msg <> 'bruce' and msg <> 'banner';

NULL <> 'bruce'无法确定，甚至无法确定，甚至不能确定

NULL <> 'bruce' can't be determined, not even true, not even false

NULL <> 'banner'无法确定，甚至不为真甚至不为假

NULL <> 'banner' can't be determined, not even true not even false

因此，空值表达式可以有效地解析为:

So the null value expression, effectively resolved to:

can't be determined AND can't bedetermined

实际上，如果您的RDBMS在SELECT上支持布尔值(例如MySQL，Postgresql)，则可以看到原因: http://www.sqlfiddle.com/#!2/d41d8/828

In fact, if your RDBMS supports boolean on SELECT(e.g. MySQL, Postgresql), you can see why: http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

返回空值.

这也返回null:

select null <> 'Bruce' and null <> 'Banner'

鉴于您正在使用NOT IN，它基本上是一个AND表达式.

Given you are using NOT IN, which is basically an AND expression.

NULL AND NULL

结果为NULL.因此，就像您正在执行以下操作: http://www.sqlfiddle.com/#!2 /0d5da/12

Results to NULL. So it's like you are doing a: http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

一无所获

这篇关于为什么MYSQL IN关键字不考虑NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！