where id <> 3 skips NULL value

Question

where id <> 3 skips NULL value

dallas13

SSCertifiable

Points: 7491
More actions
September 9, 2014 at 12:41 pm

#290504

Hello,
Please see screenshot attached. Why it is skipping NULL Value in query-2?
I want that NULL so I wrote query-3, is that correct?
Please reply. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

dallas13 (9/9/2014)
Hello,
Please see screenshot attached. Why it is skipping NULL Value in query-2?
I want that NULL so I wrote query-3, is that correct?
Please reply. Thanks.

Yes if you want to include NULL you need to do it like that. If you say Where MyColumn <> 3 it will exclude NULL because any type of equality check with a NULL results in NULL. As a result if your column has NULL it indeterminate if that row = 3 or <> 3 because the value is unknown.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

I'm a noob but Predicates only evaluate to TRUE. Also NULL is not a value. Query 3 is correct as far as my very limited knowledge goes.

So it skips in query 2 because since NULL is not a value it cant be evaluated to true. It is thus evaluated to false as it is a unknown.

***SQL born on date Spring 2013:-)

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

dallas13

SSCertifiable

Points: 7491

September 9, 2014 at 12:59 pm

#1744229

Thanks for the explanation guys.

ScottPletcher SSC Guru Points: 100948 More actions · Answer 4

thomashohner (9/9/2014)
I'm a noob but Predicates only evaluate to TRUE. Also NULL is not a value. Query 3 is correct as far as my very limited knowledge goes.
So it skips in query 2 because since NULL is not a value it cant be evaluated to true. It is thus evaluated to false as it is a unknown.

The first part is quite right: a condition in SQL must be true to be considered met, not simply "not false".

But the second part, be careful -- it's not evaluated to "false", it's evaluated to NULL also: effectively "unknown", since SQL can't determine if it's true or false.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 5

I saw that very bad wording on my part.

***SQL born on date Spring 2013:-)