isnull函数和 字段名 is null的区别

select case when [b]sc_amount is null [/b]then 0 else sc_amount end from salechance;

select [b]isnull[/b](sc_amount,0) from salechance;

运行结果：

1000
0
700
6000
3000
7000
6700
25000
0
0

和

1000

700
6000
3000
7000
6700
25000


------------

前一个如果sc_amount 是空字符串和null，一样默认为null,
后一种如果sc_amount 是空字符串，不会默认为null

有待再去验证


-----------------------------------------
说明：
1，查询某字段为null语句 select * from rdrecords where iprice is null
2,查询某字段为null和为0的语句 select * from rdrecords where isnull(iprice,N'')=N'' 查询某字段为不为null且不为0的语句 select * from rdrecords where isnull(iprice,N'')<>N''
3,查询某字段，如果为null，就用0更新后的语句 select * from rdrecords where iprcie=isnull(iprice,0)


------------------------------
select sc_amount from salechance;
查询结果是
sc_amount
1000

700
6000
3000
7000
6700
25000

null


-----
select count(*) from salechance where isnull(sc_amount,'0')='0';
select count(*) from salechance where isnull(sc_amount,0)=0;
返回结果是：
1和7

说明什么呢？待查......

[color=red]结果发现
select * from salechance where sc_amount=0;
执行的是空字符串=0为true的，空字符串<>'0'[/color]

select * from salechance where isnull(sc_amount,'0')='0';

下面2句效果一样
select * from salechance where isnull(sc_amount,0)=0;
select * from salechance where isnull(sc_amount,'0')=0;