Обсуждение: 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:

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

>
> 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

Bruce Momjian wrote:
>
> >
> > 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?

Even 'not (x in subselect)' doesn't help in Oracle! This works just
as 'x not in subselect'.

Ok, if we don't know what standard say then could you test this
in Informix, etc. Don't forget put NULL into table in subselect
to get 'no rows selected' for 'x not in subselect'...

>
> 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.

This is exactly how it works in postgres and Oracle.

Vadim

> > > 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'.
>
> Even 'not (x in subselect)' doesn't help in Oracle! This works just
> as 'x not in subselect'.
> > 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.

The SQL92 standard sez:

           1) If NOT is specified in a <boolean test>, then let BP be the
            contained <boolean primary> and let TV be the contained <truth
            value>. The <boolean test> is equivalent to:

            ( NOT ( BP IS TV ) )

However, "a NOT IN b" is not the same as "NOT (a IN b)", and my SQL book
points out that "IN" is shorthand for "=ANY" and "NOT IN" is shorthand for
"<>ALL". Also, my Date book sez:

            In general, an all-or-any condition evaluates to TRUE if and only
if
            the corresponding comparison condition without the ALL (or ANY,
            respectively) evaluates to TRUE for ALL (or ANY, respectively) of

            the rows in the table represented by the table expression.
            (NOTE: If that table is empty, the ALL conditions return TRUE,
the
           ANY conditions return FALSE).

So, it looks to me that

  WHERE x IN (SELECT y FROM empty_table)

evaluates to FALSE, and

  WHERE x NOT IN (SELECT y FROM empty_table)

evaluates to TRUE.

I looked through my two reference books and my online draft copy of the SQL92
standard, and could not find much mention of operator precendence, and even
less on "NOT" precendence. The only mention I could find was the statement

         ... Where
         the precedence of operators is determined by the Formats of this
         International Standard or by parentheses, those operators are ef-
         fectively applied in the order specified by that precedence. Where
         the precedence is not determined by the Formats or by parentheses,
         effective evaluation of expressions is generally performed from
         left to right. However, it is implementation-dependent whether ex-
         pressions are actually evaluated left to right, particularly when
         operands or operators might cause conditions to be raised or if
         the results of the expressions can be determined without completely
         evaluating all parts of the expression.

However, it wasn't clear to me whether the above statement was referring to
operators in expressions or to operators in the BNF notation used to define
the language.

Also, in a completely different place:

         Operations on numbers are performed according to the normal rules
         of arithmetic, within implementation-defined limits, except as
         provided for in Subclause 6.12, "<numeric value expression>".

I can't believe that the standard isn't more explicit somewhere about
operator precedence, or that it is strictly a left-to-right evaluation.

                                                       - Tom