Christopher Winslett：Postgres计算与NULL的歧义
内容提要
本文探讨PostgreSQL中NULL的语义及其对查询的影响。NULL表示未知值，在比较、算术、逻辑运算中会传播，导致WHERE过滤、NOT IN、聚合、窗口函数及字符串拼接等出现意外结果。通过示例展示NULL陷阱，并提出使用COALESCE、IS NOT DISTINCT FROM、NOT EXISTS等解决方案，强调合理设计NOT NULL约束的重要性。
延伸解读
NULL不是值，而是“未知”的标记
文章强调，NULL不是值，而是表示“未知”的标记。因此，任何与NULL的比较、算术或逻辑运算都会传播“未知”，导致结果也是NULL。理解这一点是避免SQL陷阱的关键。例如，NULL = NULL的结果不是TRUE，而是NULL，因为两个未知是否相等本身也是未知的。
三值逻辑下的查询陷阱
SQL使用三值逻辑（TRUE、FALSE、UNKNOWN），WHERE子句只保留结果为TRUE的行。因此，像active OR NOT active这样的恒真式在SQL中并不成立，因为当active为NULL时，整个表达式为UNKNOWN，行会被过滤掉。使用IS TRUE、IS NOT TRUE、COALESCE等可以明确处理NULL，避免意外丢失数据。
聚合函数与NULL的交互
聚合函数如SUM、AVG、COUNT(column)会忽略NULL值，而COUNT(*)则计数所有行。这可能导致计算结果与预期不符，例如AVG(rating)与SUM(rating)/COUNT(*)的结果不同。若需将NULL视为0，应使用COALESCE(rating, 0)后再聚合。理解这一行为有助于正确解读报表数据。
排序与NULL的位置
Postgres默认将NULL视为大于任何非NULL值，因此升序排列时NULL在最后，降序时NULL在最前。若希望降序时NULL在最后，需显式使用NULLS LAST。这一规则也影响索引的使用，定义索引时应与查询的排序顺序匹配，以优化性能。
Q&A
在PostgreSQL中，NULL代表什么？为什么不能用 = NULL 来比较？
NULL在PostgreSQL中表示“未知”，不是一个具体的值。因此，任何与NULL的比较（如 =、<>）都会返回NULL（未知），而不是TRUE或FALSE。所以不能用 = NULL 来判断是否为NULL，而应该使用 IS NULL 或 IS NOT NULL。
为什么在WHERE子句中，`active OR NOT active` 可能会过滤掉一些行？如何解决？
在SQL的三值逻辑中，如果active为NULL，那么 `active OR NOT active` 的结果是NULL（未知），而WHERE只保留结果为TRUE的行，所以该行会被过滤掉。解决方法是使用COALESCE将NULL转换为明确的值，例如 `WHERE COALESCE(active, false)`，或者使用 `IS NOT TRUE` 等三值逻辑判断。
在PostgreSQL中，`NOT IN` 子查询如果包含NULL值，会返回什么结果？为什么？如何避免？
如果 `NOT IN` 的子查询结果中包含NULL，那么整个 `NOT IN` 条件会返回NULL（未知），导致查询结果为空。因为 `NOT IN` 等价于多个 `<>` 条件的AND，而任何与NULL的比较都是未知。避免的方法是使用 `NOT EXISTS` 或反连接（LEFT JOIN ... WHERE ... IS NULL），或者先过滤掉子查询中的NULL值。
在PostgreSQL中，聚合函数（如AVG、SUM）如何处理NULL值？如果想把NULL当作0计算，应该怎么做？
聚合函数（如AVG、SUM、COUNT(column)）会忽略NULL值，只对非NULL值进行计算。例如，AVG(rating) 会跳过NULL。如果想把NULL当作0参与计算，可以使用 `AVG(COALESCE(rating, 0))`。但要注意，这样会改变平均值，因为NULL被当作0而不是忽略。
在窗口函数中，`lag` 或 `first_value` 遇到NULL会返回什么？Postgres 19提供了什么新特性来解决？
在窗口函数中，`lag`、`lead`、`first_value` 等函数会直接返回指定位置的值，如果该位置是NULL，则返回NULL，不会自动跳过。Postgres 19计划支持 `IGNORE NULLS` 子句，可以跳过NULL值，找到最近的非NULL值。例如：`lag(temp) IGNORE NULLS OVER (ORDER BY ts)`。
在PostgreSQL中，使用 `||` 拼接字符串时遇到NULL会怎样？有什么替代方案？
使用 `||` 拼接字符串时，如果任何一个操作数为NULL，结果就是NULL。例如 `'Hello, ' || NULL` 返回NULL。替代方案是使用 `concat` 或 `concat_ws` 函数，它们会将NULL视为空字符串。例如 `concat('Hello, ', NULL)` 返回 'Hello, '。
在PostgreSQL中，NULL在排序时默认排在哪里？如何控制NULL的排序位置？
在PostgreSQL中，NULL默认被视为比任何非NULL值都大，因此在升序排序时NULL排在最后，降序排序时NULL排在最前。可以使用 `NULLS FIRST` 或 `NULLS LAST` 来明确指定NULL的排序位置，例如 `ORDER BY points DESC NULLS LAST`。
