what wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

what wrong

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-25 : 06:02:22

create table #t1(a varchar(5),i int null)
insert into #t1 values('a',10)
insert into #t1 values('ab',1)
insert into #t1 values('ab',5)
insert into #t1 values('a',null)

select * from #t1 where i<>10

i hope null <> 10

======================================
Ask to your self before u ask someone

Teroman
Posting Yak Master

115 Posts

Posted - 2002-04-25 : 06:11:34

NULL does not equal 10, but it doesnt not equal 10 if you see what i mean.

NULL is unknown, so it may be 10, it may not

try

select *
from #t1
where i <> 10
or i IS NULL

or look up the IsNull function

col

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-25 : 06:17:52

when we are not clear about the data...
do u mean we should check if any null first and then write query
or should i keep on adding "i is null" in every thing
what can be the best suggest option

======================================
Ask to your self before u ask someone

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-25 : 08:45:50

NULL does not equal anything.
NULL does not even equal NULL

You should never try to compare NULL with anything.
The isNull function is a handy way of returning a value in the place of NULL. For your query, I would convert NULLs to something you can filter out. Something like

select * from #t1 where isNull(i, 0) <> 10

Hope that clears things up

Damian

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-26 : 01:36:50

quote:
NULL does not even equal NULL

does this mean

select * from #t1 where i is null return no rows
or
select * from #t1 where i = null

======================================
Ask to your self before u ask someone

Edited by - khalik on 04/26/2002 01:37:50

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-26 : 17:40:38

If you want to retrieve rows with null values, you need to use the key phrase IS NULL as in your example:

select * from #t1 where i is null

You will get nothing if you use "i = null".

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-27 : 03:52:06

Also, look at the ANSI Nulls setting. You can set it tou behave as you are expecting, though that may not be advisable.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources