On 6/28/07,
Alban Hertroys <
alban@magproductions.nl> 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.colname13. compare R.colname2 with the result of the correlated-subquery4. 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
