NULL versus IN (NULL) versus IS NULL

Article View: bit.databases.mssql-l

Article #232

Back to bit.databases.mssql-l Previous Next

#232

From: Dan G
Date: Mon, 06 Sep 1999 00:00

66 lines
2177 bytes

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.

Technical Details

Message-ID: <7qvuii$o43$1@pollux.ip-plus.net>

Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 10f695,d81f9450cc844161,start
X-Google-Attributes: gid10f695,public
X-Google-Thread: 107d2f,d81f9450cc844161,start
X-Google-Attributes: gid107d2f,public
X-Google-Thread: f6611,d81f9450cc844161,start
X-Google-Attributes: gidf6611,public
X-Google-Thread: 104de1,d81f9450cc844161,start
X-Google-Attributes: gid104de1,public
X-Google-Thread: 11301e,d81f9450cc844161,start
X-Google-Attributes: gid11301e,public
X-Google-Thread: 109c00,d81f9450cc844161,start
X-Google-Attributes: gid109c00,public
From: "Dan G" <Gavrilescu.Daniel@pmintl.ch>
Subject: NULL versus IN (NULL) versus IS NULL
Date: 1999/09/06
Message-ID: <7qvuii$o43$1@pollux.ip-plus.net>
X-Deja-AN: 521598380
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
X-Complaints-To: news@ip-plus.net
X-Trace: pollux.ip-plus.net 936607121 24707 194.209.91.2 (6 Sep 1999 08:38:42 GMT)
Organization: Swisscom IP+ (post doesn't reflect views of Swisscom)
NNTP-Posting-Date: 6 Sep 1999 08:38:42 GMT
Newsgroups: bit.databases.mssql-l,comp.databases.ms-sqlserver,microsoft.public.sqlserver,microsoft.public.sqlserver.programming,microsoft.public.sqlserver.server,microsoft.public.sqlserver.workshop