Silly question

Transact-SQL
1

I'm sure I'm looking at this problem in the wrong way, but I've spent too long trying to find the answer: Can anyone tell me why these two selects produce different results, provided that the values for intEvent and intParent are the same in the two sentences?

declare @tblEvents table (intEvent int, intParent int)
insert into @tblEvents (intEvent, intParent) values (10, 20)
insert into @tblEvents (intEvent, intParent) values (20, null)

declare @intEvent int = 10
declare @intParent int = null
if not @intEvent = @intParent print 'yes' else print 'no'

select t1.intEvent, t0.intEvent, t1.intParent from @tblEvents t0, @tblEvents t1
where t0.intEvent = 10 and t1.intEvent = 20
and (not @intEvent = @intParent)

select t1.intEvent, t0.intEvent, t1.intParent from @tblEvents t0, @tblEvents t1
where t0.intEvent = 10 and t1.intEvent = 20
and (not t0.intEvent = t1.intParent)

Results
intEvent intEvent intParent

     20          10        NULL

intEvent intEvent intParent

2

The question has been solved: ANSI NULLS was OFF.
When ON, the two sentences produce the same results.

3

That would be the default, and personally I think that is preferable - be interested to know what other folk here think,a nd whether they twiddle ANSI_NULLS ON at times to work around the logic test of Element1 = MightBeNULLElement2

and (not @intEvent = @intParent)

there are two choices to allowing NULLs within that code:

and (not COALESCE(@intEvent, -1234) = COALESCE(@intParent, -1234) )

but that relies on the bogus "-1234" value NEVER occurring in the data; I think it is valid if, say, "Zero and NULL are to be treated the same" (similarly with strings where empty-string and NULL are to be treated the same), in which case:

and (not COALESCE(@intEvent, 0) = COALESCE(@intParent, 0) )

Alternatively:

and (not (@intEvent = @intParent OR (@intEvent IS NULL AND @intParent IS NULL)) )

which I prefer (because the code, as written, reads to anyone else in future as the case where values are NULL being explicitly intended / accommodated.

4

Wise comments.

Thanks, Kristen!

5

ANSI_NULLS should always be left ON. Otherwise code can fail when trying to update tables with indexes.

6

Now I'm well confused! (although I agree with you 100%)

In my haste earlier i said that "The default for SET ANSI_NULLS is OFF" - according to BoL DOCs - and I clearly implied that that was desirable (which it isn't!!). The DOCs also say that "In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error"

How can the default be OFF then ... ??

I just ran

SELECT  sessionproperty('ANSI_NULLS')

and it says 1 - so its turned ON here (probably because the OPTIONS in my SQL Tool is explicit set)

When SET ANSI_NULLS is ON then SELECT MyColumn = NULL will return zero rows (even if MyColumn is NULL on some rows). As such I'm not sure how the O/P's code worked, apparently "wrongly", when ANSI_NULLS was set to OFF ... maybe I haven't read the code carefully enough!!

Anyway ... ANSI_NULLS should be set to ON and left like that, as @ScottPletcher said :slight_smile:

7

To explain:

WHERE ... and (not @intEvent = @intParent)

When ANSI_NULLS is ON, any NULL ("unknown") in a comparison yields a NULL ("unknown") result. But for a WHERE to match, the condition must be exactly true, not unknown or even not proven false, but explicitly true. Therefore, when either (or both) @intEvent or @intParent contain NULL, the result will be NULL, which of course is not true, so the row can't be SELECTed.