NULLS and <> : Discrepancies ?

</LurkingMode>

<NewbieMode>

Could some kind soul explain this to me ?

test1=# select distinct "Cle" from "Utilisateurs";
Cle
-----
1
2
3
4
(4 rows)

test1=# select distinct "CleUtil" from "Histoires";
CleUtil
---------
1

(2 rows) -- Uuhhh !

test1=# select count(*) as NbRec from "Histoires" where "CleUtil" is null;
nbrec
-------
2
(1 row) -- Ah Ahh ... I have NULLs.

test1=# select distinct "Cle" from "Utilisateurs" where "Cle" in
test1-# (select distinct "CleUtil" from "Histoires");
Cle
-----
1
(1 row) -- That's OK ...

test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in
test1-# (select distinct "CleUtil" from "Histoires");
Cle
-----
(0 rows) -- That's definitively *NOT* OK ! However

test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in
test1-# (select distinct "CleUtil" from "Histoires" where "CleUtil" is
not null);
Cle
-----
2
3
4
(3 rows) -- That's what I expected in the first place.

Could someone explain to me why not eliminating nulls destroys the
potential results of the query ? In other words, for any X not null, X
not in (some NULLs) is false.

</NewbieMode>

<LurkingMode>

Emmanuel Charpentier