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

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'





Rick Townsend
Rick Townsend
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 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
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2789 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: 2601 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 (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145961 Visits: 19425
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11615 Visits: 1085
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 Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 565
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 Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 122
To further complicate things...can you actually SET ANSI_NULLS [highlight=#ffff11]= [/highlight]ON?
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