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
Rick Townsend
Rick Townsend
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 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
Rick Townsend
Rick Townsend
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 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
Jeff Gray
Jeff Gray
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 389

What is a BOL? heh

jg


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: 2583 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





Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63244 Visits: 19115
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
My Blog: www.voiceofthedba.com
SQLBill
SQLBill
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5043 Visits: 1077
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



dde
dde
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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)



SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 530
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
Jeremy Giaco
Jeremy Giaco
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 122
To further complicate things...can you actually SET ANSI_NULLS [highlight=#ffff11]= [/highlight]ON?
Deepak Sharma-2311
Deepak Sharma-2311
Right there with Babe
Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)

Group: General Forum Members
Points: 762 Visits: 462
This question is quite wrong. The SET key word can not be used by = (EqualTo operator). The correct syntax is:
SET ANSI_NULLS OFF
this is wrong:
SET ANSI_NULLS= OFF

Deepak Kumar Sharma
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