🚀 go-pugleaf

Hi,

That's true. The story of this thread is use if it's possible "expression IS
[NOT] NULL" as ANSI92 compliant procedure.



Grzegorz Rymarz wrote in message <37D39029.3AD5DA9@hector.com.pl>...
>Hi Dan,
>behaviour of expressions containing nulls depends on current value of
>ANSI_NULLS session setting.
>If it is enabled nulls behave in the way you have noticed (SQL-92
>conformance!!).
>After switching it off, your queries will start to work according to
>your expectations...
>
>Regards,
>Grzegorz Rymarz
>
>
>
>Dan G wrote:
>
>> Hello all,
>>
>> Take a moment and read test the below puzzle (could be a good lesson
>> for
>> some of us).
>> Compare the results/performance (and query plans) and respond to the
>> questions:
>> Running on SQL 7 (SP1).
>>
>> -- case A) <> NULL examples.
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name<>NULL
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name NOT IN (NULL)
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name IS NOT NULL
>>
>> Questions:
>> A) why the execution plans are the same (same predicate) but the
>> results are
>> different (BTW last one is the correct one !).
>> MS SQL 7  shouldn't allow the first two if they give wrong results
>> ;-(.
>>
>> -- B) = NULL
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name=NULL
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name IN (NULL)
>>
>> select
>> count (*)
>> from
>> pos
>> where
>> cntc_name IS NULL
>>
>> Questions:
>> B) Same question as above + why the filtering is removed from the
>> execution
>> plan ?.
>>
>> All the best,
>>
>> Dan.
>
>
>