Nulls

Question

Post reply

Nulls

Viewing 15 posts - 16 through 30 (of 48 total)

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

honza.mf SSCertifiable Points: 5519 More actions · Answer 1

tommyh (12/8/2010)
The QOTD isnt there for us to score points...

It's easy to say this if you have one thousand points 😀

See, understand, learn, try, use efficient
© Dr.Plch

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 2

tommyh (12/8/2010)
The QOTD isnt there for us to score points... its there to teach/spread knowledge.

Exactly. I knew I was answering the question incorrectly -- it was the result I would have expected, which is never the right answer in QotDs -- but I went with that answer anyway because I wanted to learn something. I have no idea how many "points" I have, nor do I care, nor do I know what I would do with that information if I had it.

[edit: Okay, now I see how many points I have. And then what, can I trade them in for prizes like skee-ball tickets or something?]

Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

tommyh SSCertifiable Points: 6302 More actions · Answer 3

honza.mf (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points...
It's easy to say this if you have one thousand points 😀

Those points also go up from posting. So in theory i could have answered ALL QOTD wrong and have 1000... 1001 forum posts... so doesnt say that much.

/T

tommyh SSCertifiable Points: 6302 More actions · Answer 4

ronmoses (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points... its there to teach/spread knowledge.
Exactly. I knew I was answering the question incorrectly -- it was the result I would have expected, which is never the right answer in QotDs -- but I went with that answer anyway because I wanted to learn something. I have no idea how many "points" I have, nor do I care, nor do I know what I would do with that information if I had it.
[edit: Okay, now I see how many points I have. And then what, can I trade them in for prizes like skee-ball tickets or something?]
Ron

No you can only compare it to others like Jeff Moden and GilaMonster... and cry 😛

/T

honza.mf SSCertifiable Points: 5519 More actions · Answer 5

tommyh (12/8/2010)
honza.mf (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points...
It's easy to say this if you have one thousand points 😀
Those points also go up from posting. So in theory i could have answered ALL QOTD wrong and have 1000... 1001 forum posts... so doesnt say that much.
/T

Yessir. And I now obtain one more point! :w00t:

Sorry, I have a bad day (or maybe a week) and this to relax a little.

See, understand, learn, try, use efficient
© Dr.Plch

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 6

vk-kirov (12/8/2010)
The result of the comparison "EmployeeID <> NULL" is undefined for all values of EmployeeID (unless you set the ANSI_NULLS option to OFF). So the result of the WHERE clause is undefined (i.e. not true), and no rows are returned.

OK... just to let you know guys that I tought i understood the "null" chalenge because null is "unknown", but now, it seems I never understood it. :crying:

The answer of vk-kirov showed me (if I change the "and" by "or"in the positive subquery) why the answer is nothing in a negative subquery, but is something in a positive one.

select * from dbo.Employees where (EmployeeID <> NULL) and (EmployeeID <> 0) and (EmployeeID <> 1)

select * from dbo.Employees where (EmployeeID = NULL) or (EmployeeID = 0) or (EmployeeID = 1)

So I'll vote to gave 10 points to vk-kirov 😀

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 7

tilew-948340 (12/8/2010)
The answer of vk-kirov showed me (if I change the "and" by "or"in the positive subquery) why the answer is nothing in a negative subquery, but is something in a positive one.
So I'll vote to gave 10 points to vk-kirov 😀

seconded.

Dave62 SSCertifiable Points: 7459 More actions · Answer 8

tommyh (12/8/2010)
... The QOTD isnt there for us to score points... its there to teach/spread knowledge.../T

A lot of people seem to think the QOTD is only about learning, especially when the "trick" questions come up. I guess we all have different ideas of the purpose of a question.

To me, a question, exam, or quiz are all things that are designed to test what we know. They may teach us or help us to learn what we don't know once the answer is made known. But at the time of the question, exam, or quiz it's not about learning it's about seeing what we know or don't know.

Sure, we all learn a lot from the discussion that follows but that is not the point of a question it's the point of the discussion that follows.

If I want to learn something, I'll read the articles or do research. If I want to test my knowledge, I'll take an exam, quiz, or try to answer the QOTD... and then come to the discussion to learn. 😉

SanDroid SSChampion Points: 10068 More actions · Answer 9

IMO - This is another 5 STAR question.

It was simple, elegant, and without a typo in the code, question, or answers.

It has sparked three pages of discussion and debate already and it is not even 9 AM CST yet.

It mad me spend an hour searching SQL books online and then the entire MSDN library for any reference to support the answer. Which was not easily found.

There are mentions to this behavor in the MSDN Books Online at MSDN.Micro$soft.com.

Here they are for those that do not feel like they can cut and paste the answer into a search box correctly.

http://msdn.microsoft.com/en-us/library/95c6ex88(VS.80).aspx

I think this behavior started in Fox Pro, Access, Power Book, etc... but that was a while back for me to actually remember clearly.

http://msdn.microsoft.com/en-us/library/ms177634.aspx

http://msdn.microsoft.com/en-us/library/aa259187(SQL.80).aspx

Select clause SQL doc

http://msdn.microsoft.com/en-us/library/aa259187(SQL.80).aspx#_where_clause

Where clause section inside the Select docs

Nothing clearly states this behavior, but I aagree with the authors statement in the answers explination. I also agree that finding specific documentation on this is not easy.

;-)If Hugo and I agree that soemthing is correct, why would you need a clear reference? 😉

😎

nigelg99 Say Hey Kid Points: 677 More actions · Answer 10

Good question and good illustration of one of the common pitfalls when using Nulls. 🙂

Geoff A SSChampion Points: 11511 More actions · Answer 11

Geoff A

SSChampion

Points: 11511

December 8, 2010 at 11:32 am

#1260243

nice question. thanks pramod singla

SanDroid SSChampion Points: 10068 More actions · Answer 12

tilew-948340 (12/8/2010)

select * from dbo.Employees where (EmployeeID <> NULL) and (EmployeeID <> 0) and (EmployeeID <> 1)
select * from dbo.Employees where (EmployeeID = NULL) or (EmployeeID = 0) or (EmployeeID = 1)

The correct TSQL comparison syntax for checking a NULL is IS:

Examples - EmployeeID IS NOT NULL

EmployeeID IS NOT NULL

UMG Developer SSChampion Points: 13482 More actions · Answer 13

Thanks for the question, but like others said the explanation could have been a little better, like mentioning that this applies to NOT IN. (It doesn't apply directly to IN.)

Ken SSCoach Points: 17191 More actions · Answer 14

Nice question. So this QOTD just shows one more reason to use EXISTS/NOT EXISTS over the IN/NOT IN syntax.

Running this select statement:

select * from dbo.Employees where not exists (SELECT 1 FROM NullOperation WHERE NullOperation.NullableColumn = Employees.EmployeeID)

instead of the one in the QOTD, would return the expected results of 2,3,4.

Artur Komkov Mr or Mrs. 500 Points: 519 More actions · Answer 15

What's why I always try to avoid nullable columns or make something like that:

select * from dbo.Employees where EmployeeID

not in (SELECT isnull(NullableColumn,0) FROM NullOperation)