> Whats the difference?? when i ask for rows with "not id in" and my id
> is 3 and the subquery returns a NULL but no '3' than '3 is in NULL'
> ???
>
> Look at this:
>
> select 'funny' where not 3 in (NULL);
> ?column?
> ----------
> (0 rows)
>
> 3 is not in NULL!!
Actually, you don't know that. That's part of the three valued logic
and unknown NULL. Since NULL is an unknown value, you don't know
whether a 3 is equal to that NULL or not.
-- Stupid NULL related logic stuff --
The series goes like this
A NOT IN B is the same as
NOT (A IN B) is the same as
NOT (A =ANY B)
The result of A =ANY B is derived by the application of the
implied comparison predicate A = BT to every row in B [in this
case the NULL is effectively a one row values list].
If the = is true for at least one row BT in B, then
A=ANY B is true. If B is empty or if the = is false for
every row BT in B then A=ANY B is false. Otherwise A=ANY B
is unknown.
A=B is unknown if either A or B is the null value. Otherwise
A=B is true iff X and Y are equal. [simplified version of the
equality rules]
So, it does the 3=NULL comparison and gets an unknown back. There are
no other rows, so it was not true for at least one row, nor was it
false for all rows, so the =ANY is unknown. NOT (unknown) is unknown.
And the where clause returns those rows where the condition is true
so the row is not returned.
