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: 263

Filed under:
|
|
|
|

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

Related posts about is

    Related posts about null