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 Thursday, March 1, 2007 12:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:09 PM
Points: 2,577, Visits: 99

Jeff,

If the where clause is only true when the expression is true, then it's FALSE.

If NULL = NULL
    Print 'True'
Else
    Print 'False'

 




Post #348614
Posted Thursday, March 1, 2007 1:16 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

That's the whole problem with three-part logic, it adds a third option to the usual true/false pair.  ANSI SQL defines Unknown to handle the case where you're evaluating against rows where you don't know the value.  Take a real-life example:

I have a sibling, who I call Sam.  Is it a boy?  It looks like a simple yes/no question, but you have to answer "Unknown", because you don't have sufficient information.  Since ANSI NULLs represent "Unknown" values, you can't know if they match or not.  (It's counter-intuitive, I know.)

Larry's example:

If NULL = NULL
    Print 'True'
Else
    Print 'False'

seems correct because he assumed two-part logic.  But it's a false-dichotomy; try expanding Jeff's example this way instead (remembering to set ANSI_NULLS ON):

if (null = null) print 'null = null is true'

else if not (null = null) print 'not (null = null) is true'

else print 'neither is true'

That shows it better.  (null = null) isn't true, and it isn't false (not (false) would have been true).  It's neither.

Coming back to Question of the Day, I think we all agree it's poorly written.  After reviewing it, I'd like to see the question changed to:

What does the predicate in this WHERE clause evaluate to? (there are rows with NULL values, and ANSI_NULLS is ON)






Rick
townsends.ca
Post #348628
Posted Thursday, March 1, 2007 1:30 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

Tracked down the most obvious quote from BOL:

< predicate >

Is an expression that returns TRUE, FALSE, or UNKNOWN.


Copied from the page on WHERE clause Search Conditions http://msdn2.microsoft.com/en-US/library/ms173545.aspx






Rick
townsends.ca
Post #348631
Posted Thursday, March 1, 2007 1:38 PM


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

What is a BOL?    heh

jg

 

Post #348633
Posted Thursday, March 1, 2007 5:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:09 PM
Points: 2,577, Visits: 99

I understand the True, False, Unknown of Null evaluations. However, It's not UNKOWN to me.

  1. Will I receive results from the query? -- FALSE
  2. Did the question specify "ANSI NULLS = ON"? -- FALSE
  3. How does the predicate "Myid = NULL" evaluate? -- UNKNOWN
  4. How does the WHERE clause evaluate? -- (I still say FALSE)
  5. Does everyone get fired up about this? -- TRUE
  6. Will I get my points back? -- UNKNOWN to me (Only Steve Knows)
  7. Will I get over it? -- TRUE




Post #348667
Posted Friday, March 2, 2007 7:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:34 PM
Points: 31,181, Visits: 15,626
1. still FALSE
2. TRUE
3. still UNKNOWN
4. FALSE
5. Always TRUE
6. YES!!
7. One can only hope. It's just a game and not one I'd put on the resume/CV due to the moderator's inability to write great questions

Steve







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #348787
Posted Wednesday, March 14, 2007 5:33 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:59 PM
Points: 3,248, Visits: 550
This is directly from the BOL's section on SET ANSI_NULL

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.
...
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.


So, if the = or <> are used, it evaluates to FALSE, but if any other comparison is used it evaluates to UNKNOWN.

-SQLBill



Post #351651
Posted Thursday, April 5, 2007 9:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 26, 2010 6:58 AM
Points: 89, Visits: 71
But if actually when SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN why is the correct answer FALSE? At least that is what I got.

(Also, there is this other question about upgrading from MSDE to SQL 2005, after I went back to the list of questions all my answers were deleted. Had to start again)



Post #356481
Posted Wednesday, February 13, 2008 12:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
here i don't understand either!!. first there is mistake in question and that is followed by answer explanation. it says correct answer is "FALSE". AGaina in explanation it says , the where clause evalutes to UNKNOWN, neither true or false. LOOK beloew/.................
Category: T-SQL
What does this WHERE clause evaluate to? (there are rows with NULL values)

SET ANSI_NULLS = ON
select top 10 *
from MyTable
where Myid = NULL

Sorry - you were wrong


Correct answer: False
Explanation: The expression "MyID = NULL" evaluates to Unkown rather than any true or false condition.

Ref: NULL Versus NULL? - http://www.sqlservercentral.com/columnists/mcoles/2829.asp

Join the discussion about this question
Post #454885
Posted Tuesday, March 4, 2008 1:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 8:25 AM
Points: 159, Visits: 122
To further complicate things...can you actually SET ANSI_NULLS [highlight=#ffff11]= [/highlight]ON?
Post #464073
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse