CREATE TABLE [dbo].[ISNULL_TEST](
[name] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL,
[status] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into ISNULL_TEST values('AAAA','');
insert into ISNULL_TEST values('BBBB',NULL);
insert into ISNULL_TEST values('CCCC','123');
然后查询:
SELECT name, status, ISNULL(status, 0) AS Expr1
FROM ISNULL_TEST
WHERE (ISNULL(status, 0) = 0)
你会发现ISNULL()在where后面的运行和select后面的运行是不同的。
下面是查询结果
name status expr1
AAAA '' ''
BBBB NULL 0
这就让我很迷惑,在oracle中''和null是一样处理的。
=========================================
正解:
isnull(x,p)
当x为null时,取p,否则取x
相当于
case when x is null then p else x end
或者用basic描述为
if isnull(x) = true then
取 p
else
取 x
end if
你的语句 where isnull(status,0)=0 意思是取status为null或者0的记录,当然,会取到1,2两条记录。
因为2的status就为null.
为什么说1是''也会被取到呢?
因为isnull(x,p) 会将两个参数转换为一致的类型,即p转为x, 精度也与x一致. 而你写的是 isnull(status,0) 其实等同于 isnull(status,'0')
在where后的 isnull(status,'0') = 0 的比较中, varchar与int比较 根具精度转换时取高精确度(即低精准度数据隐式转为高精准度类型)的原则, 则尝试将isnull(status,'0') 的结果再转为 int.
这一点,你通过个简单的测试就能发现, 比如把第三条记录的 123 改为 a123, 就会报错.
因为 isnull('a123','0') 结果为 'a123' 再与 int 0 比较, a123 无法转换为int, 因此报错。
明白天上面两点, 对于第一条记录 isnull('','0') 因为 ''不是null,所以结果为 '' ,当 where '' = 0 这个条件比较时,尝试把''转换为int, 而''转为int就是0
所以结果中会有 1,2两条记录。
至于select的结果,为什么第一条记录不是0而是 '', 你看明白了上面我说的话,就清楚了。因为 isnull('','0') 得到的是 '', 它不需要与int数字运算,所以不会转换为int,就继续保留为int.
对于varchar类型,null与''是有区别的,这在大多数语言中也是的。你说oracle是null与''相同,只是oracle内部做了一些处理而已。
我们来看''与null的存储。
大家知道字串(其实是所有数据类型)在存储时都会有个地址,并分配一定空间 ''就是已经分配了地址,但所占空间为1个字符,用c++来说,就是只有一个字串中止符\0. 也就是对''这个值来说,变量有两个信息: 1 变量地址,2 占用空间,因为它没有值,所以只有一个终止符。
而null,表示是变量或字段并没有分配地址。
在我上面的描述中
因为isnull(x,p) 会将两个参数转换为一致的类型,即p转为x, 精度也与x一致. 而你写的是 isnull(status,0) 其实等同于 isnull(status,'0')
准确的说,应该是这样:
因为status为varchar(50)
isnull(status,0) 其实真正表示的应该是 isnull(status,cast('0' as varchar(50))
因为有隐式转换在操作里面,这些操作都被sqlserver替你完成了。 这是初学者不容易搞清楚的地方。