也来说说SQL语句中NULL的真实含义

NULL，表示不明确、未知的列值

测试表：testnull(id varchar(32))

数据库：Sybase ASA11.0

行数据(''), (NULL)

数据库选项ansinull为true(也是ASA数据库的默认选项)时，

select * from testnull where id = null

select * from testnull where id != null

结果均为空

select * from testnull where idis null

结果为(NULL)

select * from testnull where idis notnull

结果为('')

当ansinull为false时，

select * from testnull where id = null

结果为(NULL)

select * from testnull where id != null

结果为('')

从上述结果来看，NULL值确实是一个有争议的东西，但是，毫无疑问，ansinull对NULL的定义是精确的，即不能对NULL值进行等于或不等判断，无论是等还是不等，其结果都为false.

而统一的is null, is not null的含义则显然是明确的，NULL is null恒为真，非NULL is null恒为假。

再看看在Oracle中的结果：

SQL> select * from testnull where id is null;

ID
--------------------------------

SQL> select * from testnull where id is not null;

no rows selected

SQL> select * from testnull where id=null;

no rows selected

SQL> select * from testnull where id != null;

no rows selected

空字符串''在oracle中被示为NULL值了。比较怪异。

Oracle这种现象的重现过程如下：

SQL> create table testnull(id varchar(32));

Table created.

SQL> insert into testnull values('');

1 row created.

SQL> select * from testnull where id is null;

ID
----------------------------------------------------------------

SQL> select count(*) from testnull where id is null;

COUNT(*)
----------
1

SQL>

<script type="text/javascript"><!-- google_ad_client = "ca-pub-7104628658411459"; /* wide1 */ google_ad_slot = "8564482570"; google_ad_width = 728; google_ad_height = 90; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>