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

Filed under:

is

|

null

|

outer

|

join

|

t-sql

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

(1 row(s) affected)

(1 row(s) affected) a test b

(0 row(s) affected)

Output 2:

(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!

Related posts about null

How do I fix a noisy input device (Internal Mic)? snd_hda_intel - debug included

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
As the title says, I'm having trouble with a very noisy audio input device - the internal mic or any plugged in mics. So far I've narrowed it down to an problem in ALSA since my debug info is showing a lot of "null" values. Can anyone help? Debug Info: http://www.alsa-project.org/db/?f=e0c6fb7e10624bf7691aa2b405cf0d3968e56c63 Exert… >>> More
So which null equals this null, that null? maybe this null, or is it this null?

as seen on SQL Blogcasts - Search for 'SQL Blogcasts'
Tuning takes many routes and I get into some interesting situations and often make some exciting finds, see http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/05/17/just-when-you-thought-it-was-safe.aspx for an example. Today I encountered a multitude of Foreign Key constraints on a table, now… >>> More
MySql query optimization help

as seen on Stack Overflow - Search for 'Stack Overflow'
I have few queries and am not able to figure out how to optimize them, QUERY 1 select * from t_twitter_tracking where classified is null and tweetType='ENGLISH' order by id limit 500; QUERY 2 Select count(*) as cnt, DATE_FORMAT(CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00','+05:30'),'%Y-%m-%d')… >>> More
MySQL Database Query Problem

as seen on Stack Overflow - Search for 'Stack Overflow'
I need your help!!!. I need to query a table in my database that has record of goods sold. I want the query to detect a particular product and also calculate the quantity sold. The product are 300 now, but it would increase in the future. Below is a sample of my DB Table #---------------------------- #… >>> More
Null check error message as "is null" or "was null"

as seen on Stack Overflow - Search for 'Stack Overflow'
When doing null checks in Java code, and you throw IllegalArgumentExceptions for null values, what kind of message template do you use? We tend to use something like this public User getUser(String username){ if (username == null){ throw new IllegalArgumentException("username is null");… >>> More

Developer IT