SQL Null handling in predicate evaluation involving IN

[spshealy]

Greetings,

I have a question about how nulls are handled in "IN" clauses.. I
understand the operation null = anyvalue is undefined. Please examine
the following example.

drop table one
DB20000I The SQL command completed successfully.

create table one( first char(3), next1 int) in mcd_fl_coord3
DB20000I The SQL command completed successfully.

drop table two
DB20000I The SQL command completed successfully.

create table two( second char(3), next2 int) in mcd_fl_coord3
DB20000I The SQL command completed successfully.

insert into one (next1) values (2)
DB20000I The SQL command completed successfully.

insert into one (first,next1) values ('one',1)
DB20000I The SQL command completed successfully.

insert into two (next2) values (3)
DB20000I The SQL command completed successfully.

insert into two (next2) values (4)
DB20000I The SQL command completed successfully.

insert into two (second,next2) values ('two',5)
DB20000I The SQL command completed successfully.

insert into two (second,next2) values ('one',6)
DB20000I The SQL command completed successfully.

select * from one

FIRST NEXT1
----- -----------
- 2
one 1

2 record(s) selected.
select * from two

SECOND NEXT2
------ -----------
- 3
- 4
two 5
one 6

4 record(s) selected.
select first from one

FIRST
-----
-
one

2 record(s) selected.
<---- as expected

select second from two where second in (select first from one )

SECOND
------
one

1 record(s) selected.
<---- as expected

select second from two where second not in (select first from one )

SECOND
------

0 record(s) selected.

<---- not expected shouln't I get 'two'

select second from two where second not in (select first from one
where first is not null)

SECOND
------
two

1 record(s) selected.
<--- now I get the expected result when nulls are filterd in the "IN"
clause
Why is this? I presume this has something to do with the fact that
equality operations on nulls are undefined but it doesn't seem
consistant to me b/c there are nulls in the other table(second) too
which are not filterd in the last statement but I still get the
expected result. Can Anybody explain the logic here...I am probably
overlooking something obvious here...

TIA,
Scott

[Tokunaga T.]

Please search comp.databases with "nulls in subselect".