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