带有 NULL 或 IS NULL 的 IN 子句

发布于 2024-11-15 12:06:25 字数 465 浏览 3 评论 0原文

Postgres 是数据库

我可以在 IN 子句中使用 NULL 值吗？示例：

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

我想限制为这四个值。

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

我还尝试添加一个 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

有什么建议吗？

原文

Postgres is the database

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

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.

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晨光如昨 2024-11-22 12:06:25

in 语句的解析方式与 field=val1 或 field=val2 或 field=val3 相同。在那里放入 null 会归结为 field=null ，这是行不通的。

（评论，作者：Marc B）

为了清楚起见，我会这样做

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

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

(Comment by Marc B)

I would do this for clairity

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

回复收藏 0 原文

书信已泛黄 2024-11-22 12:06:25

由于运算符优先级。 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);

添加的括号可防止在 OR 之前进行 AND 绑定。如果没有其他 WHERE 条件（没有 AND），则不需要额外的括号。所接受的答案在这方面具有误导性。

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

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.

回复收藏 0 原文

倾城花音 2024-11-22 12:06:25

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

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

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.

回复收藏 0 原文

温柔少女心 2024-11-22 12:06:25

丹尼尔回答的问题完全没问题。我想留下关于 NULLS 的注释。当列包含 NULL 值时，我们应该小心使用 NOT IN 运算符。如果您的列包含 NULL 值并且您使用 NOT IN 运算符，您将不会获得任何输出。这就是这里的解释 http://www.oraclebin.com/2013/01/beware-of-nulls.html，一篇非常好的文章，我偶然发现并认为分享它。

回复收藏 0 原文

国产ˉ祖宗 2024-11-22 12:06:25

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

当心NULL。

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

我们将创建表table_a。

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

我们将创建表table_b。

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

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

将一些值插入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" 运算符。

第一个查询：

第一个查询将在内部处理，如下所示。因此，null 不会在这里产生问题，因为我的前两个操作数将计算为 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

第二个查询：

第二个查询将按如下方式处理。由于我们使用的是 "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

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

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

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

因此，在使用 NOT IN 运算符时，请务必小心列中的 NULL 值。

小心 NULL！！

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

Beware of NULLS.

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.

We will create table 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.

Now we will execute a query to check the existence of a value in table_a by checking its value from table_b using IN operator.

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 null value in the table table_b and see how the above two queries behave.

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 null value in the table. But how come it's behaving like this? Let's split the two queries into "AND" and "OR" operator.

First Query:

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

Second Query:

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

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

So always be careful about NULL values in the column while using NOT IN operator.

Beware of NULL!!

回复收藏 0 原文

萧瑟寒风 2024-11-22 12:06:25

简单：这将给出 TRUE 和 null 值：

SELECT *
FROM tbl_name
WHERE 
id_field IN ('value1', 'value2', 'value3') IS NOT FALSE;

EASY: This would give TRUE and null values:

SELECT *
FROM tbl_name
WHERE 
id_field IN ('value1', 'value2', 'value3') IS NOT FALSE;

回复收藏 0 原文

蓝天 2024-11-22 12:06:25

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

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

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)

回复收藏 0 原文