如果在比较的列中可以有空值/空值，您是否总是需要在WHERE中使用合并？ [英] Do you always need to use coalesce in a WHERE if there can be null/empty values in the columns you're comparing?
问题描述
我知道不能比较空的/空的列，所以经常我找到自己写一个查询像：
I know can't compare columns that are empty/null to others so often times I find myself writing a query like:
WHERE field2 <> '' AND coalesce(field1, '') <> coalesce(field2, '')
这是我总是这样做的方式， m好奇如果有一个更好的方法来处理null / empty值。另外，我很好奇为什么 NULL< comparison operator>
This is the way I've always done it, but I'm curious if there's a better way to handle null/empty values. Also I'm curious as to why NULL <comparison operator> anything is UNKNOWN in the first place.
推荐答案
不。使用 COALESCE（）通常可以防止使用索引。使用布尔逻辑的等效语法很麻烦。
No. The use of COALESCE() generally prevents indexes from being used. The equivalent syntax using boolean logic gets cumbersome.
最好的解决方案是使用 IS DISTINCT FROM ：
The best solution is to use IS DISTINCT FROM:
where field1 is distinct from field2
b $ b
通过使用内置运算符，Postgres可以更好地更改查询。 IS DISTINCT FROM 是ANSI标准语法，在文档。
By using built-in operators, there is a better change that Postgres can optimize the query. IS DISTINCT FROM is ANSI standard syntax which is explained in the documentation.
这篇关于如果在比较的列中可以有空值/空值，您是否总是需要在WHERE中使用合并？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
