IS NULL doesn't work as expected in MSSQL 2000 with no Service Pack on it
Posted
by user306825
on Stack Overflow
See other posts from Stack Overflow
or by user306825
Published on 2010-04-01T11:51:11Z
Indexed on
2010/04/01
11:53 UTC
Read the original article
Hit count: 260
The following batch executed on different instances of mssql 2000 illustrates the problem.
select @@version
create table a (a int) create table b (b int)
insert into a(a) values (1) insert into a(a) values (2) insert into a(a) values (3) insert into b(b) values (1) insert into b(b) values (2)
select * from a left outer join (select 1 as test, b from b) as j on j.b = a.a where j.test IS NULL
drop table a drop table b
Output 1: Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: )
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected) a test b
(0 row(s) affected)
Output 2:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected) a test b
3 NULL NULL
(1 row(s) affected)
If someone encounters the same problem - make sure you have the SP installed!
© Stack Overflow or respective owner