SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 01 Mar 2007


Question of the Day for 01 Mar 2007

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12501 Visits: 14
Comments posted to this topic are about the Question of the Day for 01 Mar 2007 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=981.
Rick Townsend
Rick Townsend
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 89
Could we have the question expanded to include a comment that ANSI_NULLS is set to ON? Because if a user ran that with ANSI_NULLS OFF, the WHERE clause would evaluate to TRUE.




Rick
townsends.ca
Hugo Felicio
Hugo Felicio
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
I'm puzzled...isn't it the same as 'where 1=2' ? doesn't it always evaluate to false and isn't that why you don't get nothing on the results ??
Jeff Gray
Jeff Gray
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2655 Visits: 389

The question is somewhat completely and utterly wrong.

A where clause does not evaluate TO anything at all. In this case there will never be any rows matching the filter (assuming the normal ANSI NULLS setting), so the best answer among all of the possible but all incorrect answers is "False". "Unknown" is not correct, because it is "known" that no rows will match.

The expression IN the where clause evaluates to Unknown, but that is not what the question asks.


Eric Shaffer
Eric Shaffer
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 17

If you want to know how many records there are with MyID that is null, you write the query this way:

select top 10 *  from MyTable where Myid IS NULL
Or to return all rows where Myid has a value:
select top 10 *  from MyTable where Myid IS NOT NULL

Larry Briscoe
Larry Briscoe
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 99

How can that question be answered the way it is stated? I've known how NULLS work for ever and still got it wrong. I'm 100% confident in how NULLS work.

Myid = NULL
and
Myid != NULL

Will both evaluate FALSE, or not give results. If I received results would that be TRUE. By not getting results is FALSE!!!

I think the Question and Answer are phrased badly and incorrect. The answer should be FALSE. How can UNKNOWN be the answer, when I know what the results would be (FALSE).

Maybe, it's only UNKNOWN to the writer. Because, I know it's FALSE. It'll always return NO ROWS, that's pretty well KNOWN.

If you get the question right, it's obvious what UNKNOWN is. How NULLS work!!!

If you got the question right. Here's how NULLS work.

Myid IS NULL (is the proper way to write the statement)

Also, I agree with Jeff Gray on the ANSI NULLS setting.

Please give me my points back!





vinurajr
vinurajr
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 211
False is the Right Answer. That we know that we will get zero rows..
Jeff Gray
Jeff Gray
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2655 Visits: 389

Hi Larry,

The expression evaluates to Unknown. It is neither true nor false.

Try this

If NULL = NULL Print 'True'
If NOT (NULL = NULL) Print 'False'

The problem with the question is that it asks what the WHERE clause evaluates to, which is only true if the expression is true.

Curiously, the explanation correctly indicates that the expression evaluates to Unknown. It's almost as if the explanation to a different question is given.


Jasmine D. Adamson
Jasmine D. Adamson
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 140
The answer for this is pretty wrong. I've been doin this stuff for a long time and all where clauses evaluate to either true or false. Always, 24/7/365, that's what you get. It's incorrect to have a where clause return NULL. Until we have computers that can simultaneously send back a row and not send back a row, our where clauses will be boolean, meaning two-valued.

The comparison evaluates to NULL, but who cares - it's the effect on the where clause that's important. I'm irked that I didn't get points for this one.
TDuffy
TDuffy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1900 Visits: 57
I agree that this is poorly written for the reasons given before me. But, I got it right without waiting for tomorrow!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search