will null's be returned using >,< statement against integer comparison

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

Transact-SQL (2000)

will null's be returned using >,< statement against integer comparison

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-09 : 08:37:50

douglas writes "SQLTeam on 7/14/2004 in The Topic
douglas writes "This may seem like a simple question, but will null's be returned when using <,> arguments (ie. can you use the second instance if x is an integer field that contains nulls?)

1. x is not Null -- bad
2. x > 0 -- want to use this instead

all my tests say yes it will work, but it seems too obvious a solution.

Any help would be appreciated.

D" "

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-09 : 08:59:14

You could just wrap it in an isnull() and do isnull(x,0) > 0

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-09 : 08:59:17

You could treat the nulls as 0 if you want

ISNULL(x, 0) > 0 or COALESCE(x, 0) > 0

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-09 : 08:59:50

Damn Rick - you beat me by 3 secs :)

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-09 : 09:11:20

it wasn't me!

Go with the flow & have fun! Else fight the flow

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-09 : 09:14:31

Yeah!
I should go on a typing course recently some people like "Spirit1" have been pipping me @ the post quite often

Duane.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-09 : 09:46:50

quote:
Originally posted by ditch

Damn Rick - you beat me by 3 secs :)

Duane.

That's an age in a transactional system..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-09 : 10:18:02

if you say

where x > 3

then NULL values will not be returned. A WHERE clause will only allow rows in which the expression evaluates to TRUE -- if it evaluates to FALSE or "unknown" (i.e., when you compare to a NULL value) the row will not be returned.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 13:23:01

quote:
1. x is not Null -- bad

Use this if you are checking for not null values in column x

quote:
2. x > 0 -- want to use this instead

Don't use this to just check for not null values in column x,
it will weed out the not null values ( as pointed out ), but also all values < 0 !
Do use this to get all not null values > 0

both statements do what they say, so use them approprietaly,
neither is bad / good

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 03:03:08

Just FYI guys.
Few days back i saw

"where x=NULL and X<>NULL" in Select statement in one of stored procedure in my database.

i dont know what programmer thought while writing this.

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 03:27:40

maybe x was a string?

quote:
Originally posted by mk_garg20

Just FYI guys.
Few days back i saw

"where x=NULL and X<>NULL" in Select statement in one of stored procedure in my database.

i dont know what programmer thought while writing this.

mk_garg

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-10 : 04:02:44

quote:
Originally posted by mk_garg20

Just FYI guys.
Few days back i saw

"where x=NULL and X<>NULL" in Select statement in one of stored procedure in my database.

i dont know what programmer thought while writing this.

mk_garg

They didn't obviously...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources