1 != NULL - 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

Transact-SQL (2000)

1 != NULL

Author

Topic

djseng
Starting Member

25 Posts

Posted - 2004-07-26 : 14:52:34

IF 1 != NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

This really screws with my head.

mem
Starting Member

28 Posts

Posted - 2004-07-26 : 15:05:00

Not a guru but i believe...If the option ANSI_NULLS is set off, then comparisons between nulls, such as NULL = NULL, evaluate to TRUE. Comparisons between NULL and any data value evaluate to FALSE.

So try this...

SET ANSI_NULLS OFF
GO

IF 1 != NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

GO
SET ANSI_NULLS OFF
GO

gives you 'Yay'

versus this...

SET ANSI_NULLS ON
GO

IF 1 != NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

GO
SET ANSI_NULLS ON
GO

gives you 'No Yay'

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 16:15:47

Use the site and do a search...there are tons on this topic....

Null is not equal to anything, inclusing itself, because it doesn't exists....

People tend to thing of null as a "thing", but it's not, it's nothing

It's not zero, space, whatever...it's the absence (that makes the heart grow fonder) of a thing

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36520

Brett

8-)

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-26 : 18:47:47

It gets even better! Try using:

IF 1 != NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

IF 1 = NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

Now that your mind is throughly confused, we can get into just WHY the SQL engine behaves this way.

Most programming languages deal with only two of the boolean states, specifically TRUE and FALSE. There are actually several more boolean states, amd comparisions with NULL fall into one of those additional categories.

The value NULL as it is used in SQL is a place holder for an unknown value. That means that the database engine doesn't know what value might be there, it only knows that there isn't any value there now. Because of that, any comparison with NULL will fail under the SQL-92 standard... That doesn't mean that the comparison is FALSE (it is actually UNKNOWN), but that the logical execution path for FALSE will be chosen.

-PatP

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 23:42:25

Pat, you're so poetic today.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

djseng
Starting Member

25 Posts

Posted - 2004-07-27 : 06:34:43

Just a weird paradox of SQL.
They could at least add a XOR operator then.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 07:29:45

Why?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-27 : 08:52:55

quote:
Originally posted by Pat Phelan

of that, any comparison with NULL will fail under the SQL-92 standard...

Careful...well qualified....but still FALSE

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-27 : 08:56:43

In an unknown sort of way.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-27 : 09:04:55

Well said, Pat.

There's tri-state logic going on here: true, false, unknown.

A regular IF statement (or CASE or WHERE clause, etc.) only handles two possibilities: the TRUE part, or the otherwise (anything other than TRUE) part. Notice I didn't say the FALSE part.

Here's an example of the tri-state logic, and a way to return a seperate result for the 3rd possibility (uknown) :

IF condition <true part> ELSE IF not (condition) <false part> ELSE <unknown part>

For example:

IF 1 = NULL
SELECT 'True'
ELSE IF Not(1= Null)
SELECT 'False'
ELSE
SELECT 'Unknown'

Of course, you are better of in general using the "is null" syntax and handling uknown's that way, but hopefully this helps shed some light on the topic.

- Jeff

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-27 : 09:06:21

So, are we having fun yet ?!?!

-PatP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-27 : 09:23:47

quote:

So, are we having fun yet ?!?!

the answer is CLEARLY "unknown" to that question!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources