Re: why does this NOT fail

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Sep 1999 07:49:10 -0400
Message-ID: <F57XN4PRtAZc66LI4fqRy2iaFnKU@4ax.com>

A copy of this was sent to "John Boehm" <jboehm_at_peabodygroup.com> (if that email address didn't require changing) On Wed, 8 Sep 1999 16:38:24 -0500, you wrote:

>Table1 (a varchar2(10),
> b varchar2(10));
>
>Table2 (c varchar2(10),
> d varchar2(10) );
>
>
>select t.* from table1 t where
>t.a in (
>select t.a from table2 x where x.d = 'aaaaa');
>
>I get rows back from a query very similar to this, where the
>table1 attribute 'a' is not in table2. I think it should fail.
>Any ideas???
>
>

It is called a corelated subquery. TABLE1 is 100% visible and referencable in the subquery. This is the intended results.

It is most frequently used with a where exists clause, for example:

select *
  from DEPT
 where exists ( select NULL

                  from EMP
                 where emp.deptno = dept.deptno
                   and emp.sal > 1000 )

that is similar to the programming logic:

   for each record in DEPT

      query EMP looking for that deptno and see if a salary > 1000 exists, 
      if so return DEPT record
      else discard DEPT record

   end loop

A corelated subquery is a form of 'looping' in SQL. It allows the 'outer' row to be passed as a parameter of sorts to the inner query.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 06:49:10 CDT