create table tb(id int,c varchar(10)) insert tb select 1,'' union all select 8,nullselect * from tb where c='' /* id c ----------- ----------- 1 */ select * from tb where c is null /* id c ----------- ----------- 8 NULL */ set ansi_nulls on select * from tb where c=null /* id c ----------- ----------- */ set ansi_nulls off select * from tb where c=null /* id c ----------- ----------- 8 NULL */ drop table tb
=NULL 一般都用于给变量赋null 值用。。 IS NULL IS NOT NULL判断用的
select * from tb where c<>'' /* id c ----------- ----------- */ select * from tb where c is not null /* id c ----------- ----------- 1 */
晕了~~?~~~~~~~~~~~~~~~~~
rtrim(col1)=NULL 永远false
rtrim(col1) is null rtrim(col1)为null时为TRUE,空字符串时为false
rtrim(col1)=NULL : 字段未赋值, 未知值NULL
rtrim(col1) is null : 能返回一个判断值,True或 False值
insert tb select 1,'' union all select 8,nullselect * from tb where c=''
/*
id c
----------- -----------
1
*/
select * from tb where c is null
/*
id c
----------- -----------
8 NULL
*/
set ansi_nulls on
select * from tb where c=null
/*
id c
----------- -----------
*/
set ansi_nulls off
select * from tb where c=null
/*
id c
----------- -----------
8 NULL
*/
drop table tb
IS NULL
IS NOT NULL判断用的
/*
id c
----------- -----------
*/
select * from tb where c is not null
/*
id c
----------- -----------
1
*/
rtrim(null值)='' true?false