Sorry for intruding, but the following question did not get much
attention on the "General" list. However, I still need the answer ...
</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
