Обсуждение: Re: Subselects open issue Nr. 5

> 5. I need in advice: if subquery introduced with NOT IN doesn't return
>    any tuples then qualification is failed, yes ?

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;

Andreas

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