Re: [HACKERS] Subselects and NOTs

>
> 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(at)candle(dot)pha(dot)pa(dot)us