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@candle.pha.pa.us