Thread

1. Re: Subselects open issue Nr. 5

   Zeugswetter Andreas IZ5 <andreas.zeugswetter@telecom.at> — 1998-02-13T17:00:25Z

   > 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
   
   

2. Re: [HACKERS] Re: Subselects open issue Nr. 5

   Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-15T11:33:09Z

   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