Thank you All for this extensive help! BTW: google helps, once you know that the construct is called "correlated subquery" - there is no way to get an answer before one knows the question :) Thenx again! -R On Thu, 2007-06-28 at 23:23 +0530, Gurjeet Singh wrote: > On 6/28/07, Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> wrote: > > This is called a 'correlated subquery'. Basically the subquery > is > performed for each record in the top query. > > Google gave me this: > http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm > > I think the sub-section titled "Example: Correlated subquery in a > WHERE Clause" is appropriate to explain our query at hand. > > Simply put, correlated queries are like nested FOR loops of any high > level programming language. > > 1. FOR( record R in result of outer-query ) > 2. execute inner query, using any R.colname1 > 3. compare R.colname2 with the result of the correlated-subquery > 4. produce R in output, iff the above comparison succeeded > > Line 2 can be treated as another FOR loop, where every record of > inner-query is being processed, and comparing the local expressions > with a column (or expression) that comes from outer query. > > The comparison in step 3 can be against any expression, with columns > or against a pure constant too! > > For example, the following query produces the name of all the > employees, who manage at least one other employee. > > select empno, ename > from emp e1 > where exists (select 1 > from emp e2 > where e2.mgr = e1.empno); > > The only thing I would add for our query is that, that the outer > SELECT of our query produces a cartesian product (no join-condition > between t1 and t2), but only one row from t2 qualifies for the join, > since the WHERE condition is on a unique column, and the correlated > subquery returns just the required value (lowest of the IDs that are > greater than current t1.ID being processed). > > I know the above one-line-paragraph may sound a bit cryptic for > someone new to correlated subqueries, but if you understand the > example in the link above, then this would start making some sense. > > > And there's probably more to find. Interestingly enough > wikipedia > doesn't seem to have an article on the subject. > > > > > > Regards, > -- > gurjeet[.singh]@EnterpriseDB.com > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com > > 17°29'34.37"N 78°30'59.76"E - Hyderabad * > 18°32'57.25"N 73°56'25.42"E - Pune > > Sent from my BlackLaptop device
