Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Question of the Day for 01 Mar 2007 Expand / Collapse
Author
Message
Posted Wednesday, February 21, 2007 10:05 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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.
Post #346443
Posted Wednesday, February 28, 2007 9:25 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 5, 2011 9:32 AM
Points: 694, 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
Post #348377
Posted Thursday, March 1, 2007 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2007 1:30 AM
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 ??
Post #348408
Posted Thursday, March 1, 2007 7:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388

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.

 

 

 

 

 

Post #348488
Posted Thursday, March 1, 2007 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:09 PM
Points: 41, 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
Post #348495
Posted Thursday, March 1, 2007 7:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:09 PM
Points: 2,577, 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!

 




Post #348500
Posted Thursday, March 1, 2007 7:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:18 AM
Points: 1,798, Visits: 194
False is the Right Answer. That we know that we will get zero rows..
Post #348501
Posted Thursday, March 1, 2007 8:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388

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.

 

Post #348514
Posted Thursday, March 1, 2007 9:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:56 PM
Points: 280, 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.
Post #348561
Posted Thursday, March 1, 2007 11:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57
I agree that this is poorly written for the reasons given before me. But, I got it right without waiting for tomorrow!


Post #348595
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse