测试不等式与可以为null的列 [英] testing inequality with columns that can be null

问题描述

所以，我今天早上问了一个问题，我没有正确的语句，所以我得到了很多反应为什么NULL相比任何东西将给NULL / FALSE。

So, I asked a question this morning, which I did not phrase correctly, so I got a lot of responses as to why NULL compared to anything will give NULL/FALSE.

我的实际问题是，什么是时间荣幸的时尚，其中db家测试不等式两列可以都为NULL。我的问题与此问题完全相反。

My actual question was, what is the time honored fashion in which db guys test inequalities for two columns that can both be NULL. My question is the exact opposite of this question.

要求如下，A和B是两列：

a）如果A和B都为NULL，它们相等，返回FALSE

b）如果A和B都不为NULL，则返回A <> B

c）如果A或B为NULL，则它们不相等，返回TRUE

The requirements are as follows, A and B are two columns:
a) if A and B are both NULL, they are equal, return FALSE
b) if A and B are both not NULL, then return A<>B
c) if either A or B are NULL, they are not equal, return TRUE

推荐答案

取决于数据类型和列的可能值：

Depending on the data type and possible values for the columns:

COALESCE(A, -1) <> COALESCE(B, -1)

诀窍是找到一个值

另一种方法是：

(A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL)

这可能是一个问题，取决于您的特定RDBMS如何处理NULL。按照ANSI标准，这应该给你你想要的，但是谁遵循标准。 ：）

This can be a problem depending on how your particular RDBMS handles NULLs. By the ANSI standard, this should give you what you want, but who follows standards anyway. :)

- 我还应该指出，使用COALESCE函数可能会在比较列时使索引的使用无效。检查您的查询计划和查询的执行情况，看看是否有问题。

P.S. - I should also point out that using the COALESCE function may invalidate the use of indexes in comparing the columns. Check your query plan and performance of the query to see if that's a problem.

P.P.S。 - 我只是注意到OMG Ponies提到Informix不支持COALESCE。这是一个ANSI标准函数我相信，但看看我上面谈到的标准...

P.P.S. - I just noticed that OMG Ponies mentioned that Informix doesn't support COALESCE. It's an ANSI standard function I believe, but see what I said above about standards...

这篇关于测试不等式与可以为null的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！