SQL 带有 NULL 或 IS NULL 的 IN 子句

IN Clause with NULL or IS NULL

提问by Phill Pafford

Postgres is the database

Postgres 是数据库

Can I use a NULL value for a IN clause? example:

我可以对 IN 子句使用 NULL 值吗？例子：

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

I want to limit to these four values.

我想限制为这四个值。

I have tried the above statement and it doesn't work, well it executes but doesn't add the records with NULL id_fields.

我已经尝试了上面的语句，但它不起作用，它可以执行但不添加带有 NULL id_fields 的记录。

I have also tried to add a OR condition but this just make the query run and run with no end in sight.

我还尝试添加 OR 条件，但这只会使查询运行和运行，看不到尽头。

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

Any suggestions?

有什么建议？

回答by Daniel A. White

An instatement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=nullwhich won't work.

一个in声明将相同解析到field=val1 or field=val2 or field=val3。在那里放一个空值将归结为field=null不起作用。

(Commentby Marc B)

（注释由马克乙）

I would do this for clairity

为了清晰，我会这样做

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

回答by Erwin Brandstetter

Your query fails due to operator precedence. ANDbinds before OR!
You need a pair of parentheses, which is not a matter of "clarity", but pure logic necessity.

由于运算符优先级，您的查询失败。AND之前绑定OR！
你需要一对括号，这不是“清晰”的问题，而是纯粹的逻辑必要性。

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 ANDbinding before OR. If there were no other WHEREconditions (no AND) you would not needparentheses. The accepted answer is a bit misleading in this respect.

添加的括号防止AND在 之前绑定OR。如果没有其他WHERE条件 (no AND)，则不需要括号。接受的答案在这方面有点误导。

回答by Ove Halseth

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

So that you eliminate the null from the check. Given a null value in id_field, the coalesce function would instead of null return 'unik_null_value', and by adding 'unik_null_value to the IN-list, the query would return posts where id_field is value1-3 or null.

这样您就可以从检查中消除空值。给定 id_field 中的 null 值，coalesce 函数将而不是 null 返回“unik_null_value”，并且通过将“unik_null_value”添加到 IN 列表中，查询将返回 id_field 为 value1-3 或 null 的帖子。

回答by Sushant Butta

The question as answered by Daniel is perfctly fine. I wanted to leave a note regarding NULLS. We should be carefull about using NOT IN operator when a column contains NULL values. You won't get any output if your column contains NULL values and you are using the NOT IN operator. This is how it's explained over here http://www.oraclebin.com/2013/01/beware-of-nulls.html, a very good article which I came across and thought of sharing it.

Daniel 回答的问题非常好。我想留下关于 NULLS 的说明。当列包含 NULL 值时，我们应该小心使用 NOT IN 运算符。如果您的列包含 NULL 值并且您使用的是 NOT IN 运算符，您将不会得到任何输出。这就是它在这里的解释http://www.oraclebin.com/2013/01/beware-of-nulls.html，这是一篇非常好的文章，我遇到并想分享它。

回答by 1000111

Note:Since someone claimed that the external link is dead in Sushant Butta'sanswer I've posted the content here as a separate answer.

注意：由于有人声称外部链接在Sushant Butta 的回答中已失效，因此我已将内容作为单独的回答发布在此处。

Beware of NULLS.

当心NULLS。

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

今天我在使用 IN 和NOT IN运算符时遇到了一个非常奇怪的查询行为。实际上，我想比较两个表并找出其中是否table b存在一个值，table a并在该列包含null值时找出其行为。所以我刚刚创建了一个环境来测试这种行为。

We will create table table_a.

我们将创建表table_a。

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

We will create table table_b.

我们将创建表table_b。

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

Insert some values into table_a.

将一些值插入到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.

将一些值插入到table_b.

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

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

Now we will execute a query to check the existence of a value in table_aby checking its value from table_busing INoperator.

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

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

Execute below query to check the non existence.

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

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

The output came as expected. Now we will insert a nullvalue in the table table_band see how the above two queries behave.

输出符合预期。现在我们将null在表中插入一个值table_b并查看上述两个查询的行为。

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

The first query behaved as expected but what happened to the second query? Why didn't we get any output, what should have happened? Is there any difference in the query? No.

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

The change is in the data of table table_b. We have introduced a nullvalue in the table. But how come it's behaving like this? Let's split the two queries into "AND"and "OR"operator.

变化是在 table 的数据中table_b。我们null在表中引入了一个值。但它怎么会这样呢？让我们将两个查询拆分为"AND"and"OR"运算符。

First Query:

第一个查询：

The first query will be handled internally something like this. So a nullwill not create a problem here as my first two operands will either evaluate to trueor false. But my third operand a = nullwill neither evaluate to truenor false. It will evaluate to nullonly.

第一个查询将在内部像这样处理。所以 anull不会在这里产生问题，因为我的前两个操作数将评估为true或false。但是我的第三个操作数a = null既不会评估true也不会false。它只会评估为null。

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

Second Query:

第二个查询：

The second query will be handled as below. Since we are using an "AND"operator and anything other than truein any of the operand will not give me any output.

第二个查询将按如下方式处理。由于我们使用的是"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 nullvalues from table table_bwhile using NOT INoperator.

那么我们该如何处理呢？我们将在使用运算符时not null从表中选择所有值。table_bNOT IN

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

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

So always be careful about NULLvalues in the column while using NOT INoperator.

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

Beware of NULL!!

小心空值！！

回答by ch2o

I know that is late to answer but could be useful for someone else You can use sub-query and convert the null to 0

我知道回答晚了，但可能对其他人有用您可以使用子查询并将 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)

回答by Roger Rowe

Nullrefers to an absence of data. Nullis formally defined as a value that is unavailable, unassigned, unknown or inapplicable (OCA Oracle Database 12c, SQL Fundamentals I Exam Guide, p87). So, you may not see records with columns containing null values when said columns are restricted using an "in" or "not in" clauses.

Null指缺乏数据。Null正式定义为不可用、未分配、未知或不适用的值（OCA Oracle Database 12c，SQL Fundamentals I Exam Guide，p87）。因此，当使用“in”或“not in”子句限制所述列时，您可能看不到包含空值的列的记录。