将t-SQL中的值与NULL进行比较

I was curious if it's legal in t-SQL to compare a NULL to a value?

我很好奇t-SQL中将NULL与值进行比较是否合法?

For instance, if I have:

例如，如果我有:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE ctx.n1 < 130

the WHERE clause in that case is always evaluated as FALSE. Is it something I can rely on?

在那种情况下，WHERE子句的值总是为FALSE。这是我可以信赖的吗?

4 个解决方案

#1

You can't compare NULL with any other value it will be result in 'UNKNOWN'.

不能将NULL与任何其他值进行比较，这会导致'UNKNOWN'。

From msdn source

从msdn源

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

NULL值表示该值是未知的。空值与空值或零值不同。没有两个空值是相等的。两个空值之间或null值与任何其他值之间的比较返回未知，因为每个空值的值都是未知的。

#2

It depends on the value of ANSI_NULLS.

它取决于ANSI_NULLS的值。

http://msdn.microsoft.com/en-us/library/ms191270%28v=sql.90%29.aspx

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN.

当设置ANSI_NULLS为ON时，一个或多个表达式为空的比较不会产生真或假;它得到的未知。

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL.

Transact-SQL支持一个扩展，它允许比较操作符在与null值比较时返回TRUE或FALSE。这个选项通过设置ANSI_NULLS来激活。当ANSI_NULLS关闭时，当ColumnA包含空值时，比较ColumnA = NULL返回TRUE;当ColumnA包含除NULL以外的值时，比较为FALSE。

#3

All boolean operations in T-Sql with null value returns 'UNKNOWN', which is recognized as false in clauses. You can use ISNULL function when you want set some default value. for example in your case:

空值T-Sql中的所有布尔操作都返回'UNKNOWN'，这在子句中被识别为false。当您想要设置一些默认值时，可以使用ISNULL函数。例如你的例子:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE isnull(ctx.n1,0) < 130

#4

The WHERE clause in the following = is also FALSE. You need to be very careful with NULLs

下面=中的WHERE子句也是FALSE。对于NULLs，您需要非常小心

WITH ctx AS
(
SELECT 123 AS n0, NULL AS n1
) 
SELECT *
FROM ctx 
WHERE ctx.n1 = NULL

#1