带有 NULL 或 IS NULL 的 IN 子句

小开

最佳答案

in语句将被解析为与 field=val1 or field=val2 or field=val3相同的语法。在这里放入一个空值会归结为 field=null，它不会起作用。

I would do this for clairity

SELECT *
FROM tbl_name
WHERE
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

小开

丹尼尔回答的问题完全没问题。我想留下一个关于 NULLS 的便条。当一个列包含 NULL 值时，我们应该注意使用 NOTIN 操作符。如果列包含 NULL 值并且使用 NOTIN 运算符，则不会得到任何输出。这是如何在这里解释 http://www.oraclebin.com/2013/01/beware-of-nulls.html，一个非常好的文章，我无意中发现并想分享它。

小开

由于运算符优先级 .AND绑定在 OR之前，查询失败！
You need a pair of parentheses, which is not a matter of "clarity", but pure 逻辑必然性.

SELECT *
FROM   tbl_name
WHERE  other_condition = bar
AND    another_condition = foo
AND   (id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL);

The added parentheses prevent AND binding before OR. If there were no other WHERE conditions (no AND) you would not need additional parentheses. The accepted answer is misleading in this respect.

小开

SELECT *
FROM tbl_name
WHERE coalesce(id_field,'unik_null_value')
IN ('value1', 'value2', 'value3', 'unik_null_value')

这样就可以从检查中消除 null。给定 id _ field 中的 null 值，合并函数将返回‘ unik _ null _ value’而不是 null，并且通过将‘ unik _ null _ value 添加到 IN 列表中，查询将返回 id _ field 为 value1-3或 null 的帖子。

小开

注意: 因为有人声称 Sushant Butta 餐厅的外部链接已经死了，所以我把这里的内容作为一个单独的回答贴在这里。

小心无效。

Today I came across a very strange behaviour of query while using IN and NOT IN operators. Actually I wanted to compare two tables and find out whether a value from table b existed in table a or not and find out its behavior if the column containsnull values. So I just created an environment to test this behavior.

We will create table table_a.

SQL> create table table_a ( a number);
Table created.

我们将创建表 table_b。

SQL> create table table_b ( b number);
Table created.

Insert some values into table_a.

SQL> insert into table_a values (1);
1 row created.


SQL> insert into table_a values (2);
1 row created.


SQL> insert into table_a values (3);
1 row created.

Insert some values into table_b.

SQL> insert into table_b values(4);
1 row created.


SQL> insert into table_b values(3);
1 row created.

现在，我们将执行一个查询，通过使用 IN运算符从 table_b检查值来检查 table_a中是否存在值。

SQL> select * from table_a where a in (select * from table_b);
A
----------
3

执行以下查询以检查不存在。

SQL> select * from table_a where a not in (select * from table_b);
A
----------
1
2

产出正如预期的那样。现在，我们将在表 table_b中插入一个 null值，并查看上面两个查询的行为。

SQL> insert into table_b values(null);
1 row created.


SQL> select * from table_a where a in (select * from table_b);
A
----------
3


SQL> select * from table_a where a not in (select * from table_b);


no rows selected

第一个查询的行为符合预期，但是第二个查询发生了什么？为什么我们没有得到任何输出，应该发生了什么？这个查询有什么不同吗？没有.

更改发生在表 table_b的数据中。我们在表中引入了一个 null值。但它怎么会变成这样？让我们将这两个查询拆分为 "AND"和 "OR"操作符。

第一个问题:

The first query will be handled internally something like this. So a null will not create a problem here as my first two operands will either evaluate to true or false. But my third operand a = null will neither evaluate to true nor false. It will evaluate to null only.

select * from table_a whara a = 3 or a = 4 or a = null;


a = 3  is either true or false
a = 4  is either true or false
a = null is null

第二个问题:

第二个查询将按如下方式处理。因为我们使用的是 "AND"操作符，而且除了 true以外的任何操作数都不会给我任何输出。

select * from table_a whara a <> 3 and a <> 4 and a <> null;


a <> 3 is either true or false
a <> 4 is either true or false
a <> null is null

So how do we handle this? We will pick all the not null values from table table_b while using NOT IN operator.

SQL> select * from table_a where a not in (select * from table_b where b is not null);


A
----------
1
2

因此，在使用 NOT IN运算符时，始终要注意列中的 NULL值。

Beware of NULL!!

小开

我知道现在回答这个问题有点晚，但是对其他人可能有用可以使用子查询并将 null 转换为0

SELECT *
FROM (SELECT CASE WHEN id_field IS NULL
THEN 0
ELSE id_field
END AS id_field
FROM tbl_name) AS tbl
WHERE tbl.id_field IN ('value1', 'value2', 'value3', 0)

小开

Null指的是缺乏数据。Null被正式定义为不可用、未分配、未知或不适用的值(OCA Oracle Database 12c，SQL 基础知识 I 考试指南，p87)。因此，当使用“ in”或“ not in”子句限制所述列时，可能不会看到包含空值的列的记录。