NULL, = NULL and IS NULL

Question

NULL, = NULL and IS NULL

derek.colley

SSCrazy Eights

Points: 8040
More actions
August 1, 2012 at 7:02 am

#260057

Hi
N00b question perhaps, but I've just had to correct a SP which used x = NULL evaluations to use x IS NULL evaluations. The query was malfunctioning when an app-side RPC passed in DBNULL() to a parameter, x was being evaluated to something else (empty string?) instead of NULL.
Does anyone have any information on the differences in how x = NULL and x IS NULL is evaluated, please?
Thanks
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

jake.mayher SSC Enthusiast Points: 140 More actions · Answer 1

NULL is unknown, therefore you cannot evaluate an equation to it. Let's say X is 2.

X=2 is True, X=3 is False, X=NULL is unknown, it isn't True or False because we just don't know what the right side evaluates to.

Stating that X IS NULL is False, we know that X is 2, therefore stating X IS NOT NULL is True, because X is known, it is 2.

Make sense?

Jake

GSquared SSC Guru Points: 260824 More actions · Answer 2

If you turn off ANSI_NULLS settings in your query environment, you can do "X = NULL" type tests. Details here: http://msdn.microsoft.com/en-us/library/ms188048.aspx

It is strongly recommended that ANSI_NULLS be turned on, because the option to turn them off is going to go away in the future.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

GSquared (8/1/2012)
If you turn off ANSI_NULLS settings in your query environment, you can do "X = NULL" type tests. Details here: http://msdn.microsoft.com/en-us/library/ms188048.aspx

Thanks, this is exactly it. The environment in which I tested the query worked fine (ANSI_NULLS was off) but the deployment environment did not, as the setting was on.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Glad I could help. You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You may want to fix that setting.

Important:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dan-572483 SSCoach Points: 15870 More actions · Answer 6

Interesting... I've always used "<value> IS NULL" having learned T-SQL from an application I supported. Using the default configuration "<value> = NULL" doesn't genertate an error but evaluates as false even when the value is null.

Another NULL gotcha to be aware of.

drew.allen SSC Guru Points: 76984 More actions · Answer 7

dan-572483 (8/1/2012)
Interesting... I've always used "<value> IS NULL" having learned T-SQL from an application I supported. Using the default configuration "<value> = NULL" doesn't genertate an error but evaluates as false even when the value is null.
Another NULL gotcha to be aware of.

No, it evaluates to unknown. "NOT (field = NULL)" does not return TRUE, which it would if the "field = NULL" evaluated to FALSE.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dan-572483 SSCoach Points: 15870 More actions · Answer 8

Good point. As long as the variable value is not set, the following code resturns the same result whether the word NOT is there or not. Use IS NULL, and the NOT changes the result:

declare @var as varchar(5)

--set @var = 'value'

If NOT(@var = null)

Begin

Print 'The IF evaluated true'

End

Else

select @var

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Yes, because IF (@Var = NULL) returns UNKNOWN (not true, not false). NOT UNKNOWN is still UNKNOWN.

declare @var as varchar(5)

--set @var = 'value'

If (@var = null)

Begin

Print 'The var = null'

End

If NOT (@var = null)

Begin

Print 'The var doesn''t = null'

End

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass