带有 NULL 或 IS NULL 的 IN 子句 [英] IN Clause with NULL or IS NULL

问题描述

Postgres 是数据库

Postgres is the database

我可以在 IN 子句中使用 NULL 值吗?例子:

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

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

我想限制为这四个值.

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

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

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

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

有什么建议吗?

推荐答案

in 语句将被解析为与 field=val1 或 field=val2 或 field=val3 相同的.在那里放一个 null 将归结为 field=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.

(评论来自马克 B)

我会这样做是为了清晰

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

这篇关于带有 NULL 或 IS NULL 的 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！