>
> Oracle 6, there is NULL into table b:
>
> SQL> select * from a where x in (select * from b);
>
> X
> ----------
> 2
>
> SQL> select * from a where x not in (select * from b);
>
> no rows selected
>
> SQL> select * from a where not x in (select * from b);
>
> no rows selected
>
> Is 'not X in' the same as 'X not in' ? Currently we have:
I am not sure about this, but I believe 'not X in subselect' is
evaluated as 'not (x in subselect)' and not as 'X not in subselect'. Am
I missing something?
There is also some interesting stuff about comparisons:
( 1,2,NULL) = (3, NULL,4) false
( 1,2,NULL) < (3, NULL,4) true
( 1,2,NULL) = (1, NULL,4) unknown
( 1,2,NULL) > (NULL, 2,4) unknown
This happens because the comparisons are:
left < right is true of and only if there exists some j such
that Lj < Rj is true and for all i < j, and Li = Ri is true
so it seems it compares these things from left to right, trying to make
the comparison. For = and <>, is doesn't matter, but for the <, it does
matter.
Also they show:
select *
from test
where x <> (select y
from test2)
When test2 returns no rows, the query returns no rows because the
subquery returns a single row of NULL values.
Hope this helps. I can give more detail if you want it.
>
> vac=> select * from a where not x in (select * from b);
> x
> -
> 1
> (1 row)
>
> : subselect clause is "atomic" and NOT-s are never pushed into it.
>
> Once again - what standard says ?
>
> Vadim
>
>
--
Bruce Momjian
maillist@candle.pha.pa.us
