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