Xref: alice comp.databases.oracle.misc:40637 Path: alice!news-feed.fnsi.net!enews.sgi.com!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail From: Thomas Kyte Newsgroups: comp.databases.oracle.misc Subject: Re: why does this NOT fail Date: Thu, 09 Sep 1999 07:49:10 -0400 Organization: Oracle Service Industries Lines: 56 Message-ID: References: Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: inet16.us.oracle.com 936877699 16444 138.1.114.204 (9 Sep 1999 11:48:19 GMT) X-Complaints-To: usenet@inet16.us.oracle.com NNTP-Posting-Date: 9 Sep 1999 11:48:19 GMT X-Newsreader: Forte Agent 1.6/32.525 A copy of this was sent to "John Boehm" (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@us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle Corporation