Postgres在WHERE id！= int查询中排除NULL [英] Postgres excludes NULL in WHERE id != int query

问题描述

昨天我在尝试从统计表中过滤出用户ID时遇到了一个Postgres奇怪的问题。例如，当我们执行 user_id！= 24 时，postgres排除了 user_id 为 null 。

I came up against a strange problem in Postgres yesterday when trying to filter out user ids from a stats table. When we did, for example, user_id != 24, postgres excluded the rows where user_id is NULL as well.

我创建了以下测试代码，显示了相同的结果。

I created the following test code which shows the same results.

CREATE TEMPORARY TABLE test1 (
    id int DEFAULT NULL
);

INSERT INTO test1 (id) VALUES (1), (2), (3), (4), (5), (2), (4), (6), 
    (4), (7), (5), (9), (5), (3), (6), (4), (3), (7), 
    (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);     

SELECT COUNT(*) FROM test1;

SELECT id, COUNT(*) as count
FROM test1
GROUP BY id;

SELECT id, COUNT(*) as count
FROM test1
WHERE id != 1
GROUP BY id;

SELECT id, COUNT(*) as count
FROM test1
WHERE (id != 1 OR id IS NULL)
GROUP BY id;

第一个查询只计算所有行。
秒计数每个值的数目，包括空值。
第三个排除值1以及所有空值。
第四种方法是排除值1并仍包含空值。

The first query just counts all the rows. The second counts the number of each value, including nulls. The third excludes the value 1 and also all the nulls. The fourth is a work around to exclude value 1 and still include the nulls.

对于我要用于此查询的内容，空值应总是被包括在内。

For what I'm trying to use this query for, null values should always be included.

这是唯一实现此目的的方法吗？这是Postgres的预期行为吗？

Is the work around the only way to do this? Is this expected Postgres behaviour?

推荐答案

您的解决方法是通常的解决方法。一切都按预期进行。

Your "work around" is the usual way to do it. Everything is behaving as expected.

原因很简单：null既不等于也不等于任何东西。当您认为null表示未知，并且与未知值进行比较的真相也未知时，这是有道理的。

The reason is simple: nulls are neither equal, nor not equal, to anything. This makes sense when you consider that null means "unknown", and the truth of a comparison to an unknown value is also unknown.

推论是：

• null = null 不正确


• null = some_value 是不正确的


• null！= some_value 是不正确的

• null = null is not true
• null = some_value is not true
• null != some_value is not true

两个特殊比较 IS NULL 和 IS NOT存在NULL 来测试列是否为 null 。没有其他对null的比较是正确的。

The two special comparisons IS NULL and IS NOT NULL exist to deal with testing if a column is, or is not, null. No other comparisons to null can be true.

这篇关于Postgres在WHERE id！= int查询中排除NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！