select * from a where id = null

[gadev]

select * from a where id = null

问下，什么情况下能满足着条件？
id = null 。。。
并不会报语法错误啊~

[gadev]

不报错，就应该有他的意义吧

[仙道彰]

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,id1 int)
go
insert tb SELECT 
 1   ,      1    UNION ALL SELECT 
 1   ,      2    UNION ALL SELECT 
 null,      3    UNION ALL SELECT 
 null,      1    UNION ALL SELECT 
 2   ,      2   UNION ALL SELECT 
 2   ,      3
go

select * from tb where  id=null
/*id          id1
----------- -----------

(0 行受影响)*/

[华夏小卒]

declare @a table(name int,id varchar(11))
insert @a select
1,'null'union all  select 
2, null union all  select 
3,'null'union all  select 
4,'    'union all  select 
5,'null' 


select * from @a where id is null
-- 2   NULL	

select * from @a where id=null
--  	        空记录

select * from @a where id='null'
--1	null	
--3	null	
--5	null	  	        

[仙道彰]

--应该是 where id is null

[sz_free_bird]

null=UNKNOWN
所有查询筛选器都把UNKNOWN作为当做false

[仙道彰]

create table test123
(
id int,
word varchar(50),
)
go
insert into test123 (id,word)values(1,'1')
insert into test123 (id,word)values(2,'2')
insert into test123 (id,word)values(3,'3')
insert into test123 (id,word)values(4,'4')
insert into test123 (id)values(6)
insert into test123 (id)values(7)
insert into test123 (id)values(8)
insert into test123 (id)values(9)
insert into test123 (word)values('10')

select * from test123
/*id          word
----------- --------------------------------------------------
1           1
2           2
3           3
4           4
6           NULL
7           NULL
8           NULL
9           NULL
NULL        10

(9 行受影响)
*/
select * from test123 where   id is null 
/*id          word
----------- --------------------------------------------------
NULL        10

(1 行受影响)
*/
select * from test123 where   id is not null
/*id          word
----------- --------------------------------------------------
1           1
2           2
3           3
4           4
6           NULL
7           NULL
8           NULL
9           NULL

(8 行受影响)

*/
select * from test123 where word is null
/*id          word
----------- --------------------------------------------------
6           NULL
7           NULL
8           NULL
9           NULL

(4 行受影响)
*/
select * from test123 where word is not null

/id          word
----------- --------------------------------------------------
1           1
2           2
3           3
4           4
NULL        10

(5 行受影响)
/

[仙道彰]

SQL Server的Null值的比较运算的。一般情况下我们查询空值或者非空值的时候，用的是is null/is not null

[wjswshujun]

Drop table a
CREATE TABLE a(
BID	INT ,
id int  DEFAULT null
)
Go

INSERT INTO a(BID) values(123)

select * from a where id is null 

[yewutian]

受教

[htl258_Tony]

create table test123
(
id int,
word varchar(50),
)
go
insert into test123 (id,word)values(1,'1')
insert into test123 (id,word)values(2,'2')
insert into test123 (id,word)values(3,'3')
insert into test123 (id,word)values(4,'4')
insert into test123 (id)values(6)
insert into test123 (id)values(7)
insert into test123 (id)values(8)
insert into test123 (id)values(9)
insert into test123 (word)values('10')

select * from test123
/*
id          word
----------- --------------------------------------------------
1           1
2           2
3           3
4           4
6           NULL
7           NULL
8           NULL
9           NULL
NULL        10

(9 行受影响)
*/
--1.UPDATE

update test123 set 
	word=null
where id=1

select * from test123
/*
id          word
----------- --------------------------------------------------
1           NULL
2           2
3           3
4           4
6           NULL
7           NULL
8           NULL
9           NULL
NULL        10

(9 行受影响)
*/
--2.SELECT
select * from test123 where word is null
/*
id          word
----------- --------------------------------------------------
1           NULL
6           NULL
7           NULL
8           NULL
9           NULL

(5 行受影响)
*/

借用16楼测试数据

[叶子]

if(null=null)
select 'null=null' as result
else
select 'null<>null'

if(null is null)
select 'null is null'
else
select 'null is not null'



--所以在where后面作为判断条件的时候
--例如 [value]=null  当[value]值为空的时候不成立

--where的时候用is 判断 set的时候用=赋值。

[饮水需思源]

--NULL作为条件查询时：
select * from 表名 where 字段名 is NULL
--NULL为赋值时：
update 表名 set 字段名=NULL where ...

[水族杰纶]

set ansi_nulls on
declare @t table(ID int )
insert @t select 1
insert @t select null
insert @t select 2
select * from @t where id=null
set ansi_nulls off
select * from @t where id=null
/*ID
-----------

(0 個資料列受到影響)

ID
-----------
NULL

(1 個資料列受到影響)
*/

[soft_wsx]

where id is null

[gadev]

[Quote=引用 23 楼 wufeng4552 的回复:]
SQL codeset ansi_nullsondeclare@ttable(IDint )insert@tselect1insert@tselectnullinsert@tselect2select*from@twhere id=nullset ansi_nullsoffselect*from@twhere id=null/*ID
-----------

(0 個資料列受到影響)

ID
--¡­
[/Quote]

set ansi_nulls off
这样就对了 @_@

[水族杰纶]

一分沒有？

[gadev]

结贴了才发现。。。。
不好意思

[冰岛男孩]

mark

[Tomzzu]

改为 id is null 或 isnull(id, 0)=0
判断一个变量是否为NULL, 应用 is 关键字, 无论在where 后还是在 case when 语句中

[仙道彰]

[Quote=引用 26 楼 wufeng4552 的回复:]
一分沒有？
[/Quote]
确实有点亏呵，貌似水哥说的最好