问题描述
在访问MDB文件的VB6程序中，正在执行以下SQL查询:
In a VB6 program accessing an MDB file, the following SQL query is being executed:
> Select * FROM [table1] WHERE ([type] = 1 OR [type] = 2 OR [type] = 6)
> AND ([notes] = Null OR [notes] = '0') AND [date] >=
> cvdate('09/03/2013') ORDER BY [date], [column2]
如果我在程序中引用了Microsoft Access 14.0 Object Library，则返回的记录集将具有0行.
If I reference Microsoft Access 14.0 Object Library in the program the returned recordset has 0 rows.
如果我引用Microsoft DAO 3.51 Object Library，则返回的记录集将超过100行.
If I reference Microsoft DAO 3.51 Object Library the returned recordset has over 100 rows.
造成这种差异的原因是什么?两家提供商处理Null的方式之间有区别吗?对于ACE DAO访问较旧的MDB文件来说，这是一个重大变化吗?
What is the reason for this difference? Is there a difference between the way the two providers handles the test for Null? Is this a breaking change for ACE DAO accessing older MDB files?
推荐答案
WHERE ... [notes] = Null是非标准SQL. 空传播可能会强制任何涉及Null的表达式返回.因此，表达式[notes] = Null(您打算作为布尔表达式)可以很好地返回Null，它既不是True也不是False.
WHERE ... [notes] = Null is non-standard SQL. Null propagation can potentially force any expression involving Null to return Null. Therefore the expression [notes] = Null (which you intended to be a boolean expression) could very well return Null, which is neither True nor False.
查询处理器处理Null值的方式实际上可能与一个数据库引擎不同:它可以将Null解释为False，或者可以忽略结果，或者可以触发错误.还请注意，如果...
How the query processor handles that Null value may indeed differ from one database engine to another: it could interpret Null as False, or it could just ignore the result, or it could trigger an error. Note also that null propagation could collapse your entire WHERE clause to Null if...
(some other condition) AND (Null)
...评估为Null.
标准SQL将为([notes] IS NULL)，而等效于Jet/ACE的将为IsNull([notes]).这两个都将始终返回True或False.
Standard SQL would be ([notes] IS NULL) and a Jet/ACE equivalent would be IsNull([notes]). Both of these will always return either True or False.
这篇关于检查Null值时结果不一致(Jet DAO与ACE DAO)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！