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

问题描述

Postgres是数据库

Postgres is the database

我可以将NULL值用于IN子句吗?例如:

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 or field=val2 or 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.

(评论通过我这样做是出于机智

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

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