Comparing with NULL

Question

Post reply

Comparing with NULL

Thomas Franz

Hall of Fame

Points: 3897
More actions
September 25, 2016 at 3:23 am

#330487

Comments posted to this topic are about the item Comparing with NULL
God is real, unless declared integer.
September 26, 2016 at 4:40 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 1

Iulian -207023

SSCertifiable

Points: 7639

September 25, 2016 at 11:16 pm

#1903014

Nice one

it's null's fault 🙂

Rune Bivrin SSCrazy Eights Points: 8539 More actions · Answer 2

Nice question, but the correct answer is as usual: "It depends". In this case on the setting of ANSI_NULLS.

Just because you're right doesn't mean everybody else is wrong.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Rune Bivrin (9/26/2016)
Nice question, but the correct answer is as usual: "It depends". In this case on the setting of ANSI_NULLS.

Good one! SET ANSI_NULLS ON / OFF; makes a difference

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

A simple question to highlight an important concept. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 5

One of my reoccurring frustrations is that very few people understand that NULL is not a value. It is the absence of a value.

It doesn't really seem that hard, does it?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

Stewart "Arturius" Campbell (9/26/2016)
Nice question, thanks Thomas.
The ANSI NULLS setting, will, of course, impact the final outputs.

Didn't know that. Makes sense.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

Should be something like "is null" for the compare. Another gotcha would be to do something like CASE X WHEN NULL, which fails.

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Revenant

SSC-Forever

Points: 42467

September 26, 2016 at 9:50 am

#1903122

Very witty. Thanks, Thomas!

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 9

Updated the question to say ANSI_NULLS set to ON.

Note, at some point, this will be the only setting allowed.

sknox SSChampion Points: 12388 More actions · Answer 10

Steve Jones - SSC Editor (9/26/2016)
Updated the question to say ANSI_NULLS set to ON.
Note, at some point, this will be the only setting allowed.

Thanks, Steve! While I think it's fair* to assume deprecated features are not used unless explicitly stated, it's always best if the question is clear.

For reference on deprecated features: https://msdn.microsoft.com/en-us/library/ms143729.aspx

(Personally, I'm a little disappointed that SET ANSI_NULLS OFF, SET ANSI_PADDING OFF, and SET CONCAT_NULL_YIELDS_NULL OFF are still supported in SQL 2016, let alone the next version.)

*Fair in terms of a QotD - in real-world work, always check!

webrunner SSC-Dedicated Points: 31887 More actions · Answer 11

Thanks, great question.

- webrunner

P.S. I see the following code returns false, true.

SELECT CASE

WHEN (9999 IS NULL) THEN

'true'

ELSE

'false'

END,

CASE

WHEN NOT (9999 IS NULL) THEN

'true'

ELSE

'false'

END;

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

Easy one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP