Michael Meskes wrote:
>
> > 5. I need in advice: if subquery introduced with NOT IN doesn't return
> > any tuples then qualification is failed, yes ?
>
> Do you mean something like this:
>
> select * from table1 where x not in (select x from table2)
>
> table1.x: a,b
>
> table2.x is empty
>
> The correct answer IMO is 'a,b' in this case.
Ok. I'll fix this. As I see, this is exactly what Oracle 6 does, but
Zeugswetter Andreas SARZ wrote:
>
> Informix treats the subselect as NULL if no rows are returned.
> Therefore all parent rows that are not null are returned.
>
> select * from taba where a not in (<a select returning no row>); --
> is same as
> select * from taba where a is not null;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Oracle returns tuples with A being NULL!!! and more of that (table B is empty):
SQL> select count(*) from a where x > ALL (select * from b);
COUNT(*)
----------
2
and result is the same for all OP-s with ALL modifier... And
SQL> select count(*) from a where x in (select * from b);
COUNT(*)
----------
0
having tuple with NULL in X...
Who's right ?
What standard says ?
Vadim