SQL查询DISTINCT时多个NULL值被折叠的解决方法

SQL标准里有个“坑”，很多人都踩过：用DISTINCT去重时，所有NULL值会被视为相同的，无论表里有多少行是NULL，最终结果只给一个。这根本不是Bug，而是规范行为，但常常让人误以为“数据丢了”。

话说回来，你到底想要的是“技术去重”还是“业务分类”？把这个想清楚，比改SQL语句本身更重要。

DISTINCT对NULL的处理规则是什么

标准SQL白纸黑字写着：DISTINCT在去重时，把所有NULL当成相等值。100行某列都是NULL？SELECT DISTINCT col FROM t只返回一个NULL。这不是Bug，是规范，但也因此经常被误解为“数据丢失”。

常见的错误现象：

• 你预期看到多个“空状态”，比如不同原因导致的缺失（NULL、空字符串、占位符），结果全被压成一行NULL。
• 改用GROUP BY替代DISTINCT后发现结果一样，第一反应是数据库有Bug——其实不是，它们遵循的规则相同。

关键点可以肯定：所有主流引擎（PostgreSQL、MySQL 8.0+、SQL Server、Oracle）都严格遵循这一规则。SQLite默认也遵循，除非你特意去改PRAGMA nulls_first = OFF（但很少有人这么做）。

想保留多个NULL逻辑区分，该用什么替代方案

不能靠改DISTINCT行为，得换表达意图的方式：

• 如果目标是“把真正缺失和其他占位值区分开”，先统一清洗：

  SELECT DISTINCT COALESCE(col, '') AS col_clean FROM t

  这样NULL会被替换成一个显式标识，不再被折叠。
• 如果要统计每种空值来源（比如日志中NULL、'N/A'、''），用CASE WHEN显式分类：

  SELECT CASE WHEN col IS NULL THEN 'explicit_null' WHEN col = '' THEN 'empty_string' ELSE col END AS category FROM t

• 如果只是想避免NULL被折叠而又要保持原值，加一列唯一标识：

  SELECT DISTINCT col, id FROM t

  前提是id能区分行。

注意：加ROW_NUMBER()或GENERATE_SERIES()等生成序号再DISTINCT没用——只要col值相同（含NULL），仍会被合并。

MySQL和PostgreSQL在NULL去重上有什么实际差异

表面上没差异，但底层行为会影响查询计划：

• MySQL 8.0+的DISTINCT会走临时表加排序，遇到大量NULL时排序开销略高，因为NULL在B-tree中的位置不固定。
• PostgreSQL使用哈希去重，NULL被哈希到同一桶，效率更高。但若字段定义了COLLATE "C"，且存在非ASCII空值（如x00），可能被误判为等价于NULL。

实操建议：

• 在PostgreSQL中，如需严格区分NULL和空白字符串，确保字段类型不含默认COLLATE，或显式写col COLLATE "default"。
• 在MySQL中，避免在DISTINCT字段上建前缀索引（如VARCHAR(255)加INDEX(col(10))），否则NULL和短空值可能因截断产生哈希冲突。

为什么ORDER BY + DISTINCT组合有时让NULL跑最前面

这不是DISTINCT的问题，而是ORDER BY对NULL的排序策略不同：

• PostgreSQL默认NULLS FIRST（除非显式写NULLS LAST）。
• MySQL和SQL Server默认NULLS LAST（但MySQL 8.0以前不支持NULLS FIRST/LAST语法，靠隐式规则）。

所以这个语句：

SELECT DISTINCT col FROM t ORDER BY col

在PostgreSQL中，结果里NULL一定排第一；在MySQL中，NULL排最后——但DISTINCT结果本身不变，只是展示顺序不同。

容易踩的坑：

• 用ORDER BY col DESC时，误以为NULL会排末尾（其实PostgreSQL还是第一，除非加NULLS LAST）。
• 在应用层依赖排序结果做分页，没意识到数据库间NULL位置不一致，导致翻页错位。

NULL的“相等性”是SQL语义的根基，改不了。能改的只有你怎么定义“空”的业务含义。别试图绕过标准，先厘清你要的到底是“技术去重”还是“业务分类”。