SQL Null 是否大于任何日期数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9629864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is Null Greater Than Any Date Data Type?
提问by John Isaiah Carmona
I have this query in DB2
我在DB2 中有这个查询
SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A
If the SYSDATEis NULL, would it be greater than any value of @A, assuming that @Aand SOMESCHEMA.SOMETABLE.SYSDATEis a Datedata type?
如果SYSDATE是NULL，那将是比任何值@A，假设@A和SOMESCHEMA.SOMETABLE.SYSDATE是一个日期数据类型？
Please help. Thanks in advance.
请帮忙。提前致谢。
采纳答案by Eggi
Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both columns contain an equal non-null value. If both columns are null, the result will be false because null is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal non-null value and also when both columns are the null value.
另一个可用于比较可以包含 NULL 值的值的谓词是 DISTINCT 谓词。如果两列包含相等的非空值，则使用正常的相等比较 (COL1 = COL2) 比较两列将是 true。如果两列都为空，则结果将为假，因为空永远不等于任何其他值，甚至不等于另一个空值。使用 DISTINCT 谓词，空值被认为是相等的。所以 COL1 is NOT DISTINCT from COL2 如果两列都包含一个相等的非空值并且两列都是空值，那么 COL1 is NOT DISTINCT 将为真。
That means that all comparison operations will be false because you are comparing an unknown value to something. So no matter which comparison you use (only the IS NULL/IS NOT NULL operation do work!), it will be false.
这意味着所有比较操作都将是错误的，因为您将未知值与某物进行比较。因此，无论您使用哪种比较（只有 IS NULL/IS NOT NULL 操作有效！），它都会是假的。
If you want the query to work you should use something like
如果您希望查询工作，您应该使用类似
SELECT *
FROM SOMESCHEMA.SOMETABLE
WHERE COALESCE(SYSDATE, TIMESTAMP_FORMAT('0001-01-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) > @A
回答by Vince
Another possibility is to use IS NULLto test if a value is null:
另一种可能性是用来IS NULL测试一个值是否为空：
SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A OR SYSDATE IS NULL
will include the value in your set of returned values, instead of COALESCEfunction. It works only with simple cases though.
将在您的返回值集中包含该值，而不是COALESCE函数。不过它只适用于简单的情况。
回答by Klyuch
You can use this solution for comparing of two nullable dates (P.EndDate,C.EndDate):
您可以使用此解决方案来比较两个可为空的日期 (P.EndDate,C.EndDate)：
[MinDate] =
CASE
WHEN
ISNULL(C.EndDate,P.EndDate) <= ISNULL(P.EndDate,C.EndDate)
THEN
ISNULL(C.EndDate,P.EndDate)
ELSE
ISNULL(P.EndDate,C.EndDate)
END
