为什么将SQL日期变量与null进行比较会以这种方式运行？

I just came across an interesting problem with a procedure I am writing in SQL.

我刚刚遇到一个有趣的问题,我正在用SQL编写一个程序。

In my proc I have 2 dates, which are optional params defaulted to NULL, I want to check if these params are not null and if not run part of my proc, if they are null then the extra part of the proc is ignored.

在我的proc中我有2个日期,这是可选参数默认为NULL,我想检查这些参数是否为空并且如果不是我的proc的一部分,如果它们为null则则忽略proc的额外部分。

I did a fairly basic IF(@dateVariable <> NULL AND @DateVariable2 <> NULL) statement, but the if statement never works even if the variables are not null, I would assume SQL is struggling to compare the date to a NULL which is strange since datetime is nullable.

我做了一个相当基本的IF(@dateVariable <> NULL AND @ DateVariable2 <> NULL)语句,但if语句永远不会工作,即使变量不为null,我会假设SQL正在努力将日期与NULL进行比较,这是奇怪,因为datetime可以为空。

To get around this I just did IF(DateVariable IS NOT NULL) which works correctly. I also tried IF( ISNULL(@DateVariable,'') <> '') which also works correctly

为了解决这个问题,我只是做了正确的IF(DateVariable IS NOT NULL)。我也试过IF(ISNULL(@DateVariable,'')<>''),它也能正常工作

So my question is why does the first IF not work, but the second and third IF both do since both must at some point compare the contents of the variable to null?

所以我的问题是为什么第一个IF不起作用,但第二个和第三个IF都这样做,因为两者都必须在某个时候将变量的内容比较为null?

Example:

----- Fails -----

-----失败-----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date <> NULL)
BEGIN
    print('a')
END

----- Works -----

-----作品-----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (ISNULL(@Date,'') <> '')
BEGIN
    print('a')
END

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date IS NOT NULL)
BEGIN
    print('a')
END

2 个解决方案

#1

Simply put 'NULL' does not equal 'NULL'. 'NULL' is comparable to a state of uncertainty, where one thing being uncertain does not necessarily equal something else that is also uncertain. Use 'IS NULL', 'ISNULL()', or 'COALESCE()' when testing for nulls. Setting ANSI_NULLS to 'off' can change this behavior, but it is not the ANSI SQL standard. See http://msdn.microsoft.com/en-us/library/ms191270.aspx for more info.

简单地说'NULL'不等于'NULL'。 “NULL”与不确定状态相当,其中一个不确定的东西不一定等于其他不确定的东西。在测试空值时使用'IS NULL','ISNULL()'或'COALESCE()'。将ANSI_NULLS设置为“off”可以更改此行为,但它不是ANSI SQL标准。有关详细信息,请参阅http://msdn.microsoft.com/en-us/library/ms191270.aspx。

#2

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

比较空值时必须小心。比较的行为取决于SET ANSI_NULLS选项的设置。

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. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL.

当SET ANSI_NULLS为ON时,一个或多个表达式为NULL的比较不会产生TRUE或FALSE;它产生未知。这是因为未知的值无法与任何其他值进行逻辑比较。如果将表达式与文字NULL进行比较,或者比较两个表达式并且其中一个计算结果为NULL,则会发生这种情况。

See NULL Comparison Search Conditions

请参见NULL比较搜索条件