Re: a JOIN on same table, but 'slided over'

On 6/28/07, Alban Hertroys <alban(at)magproductions(dot)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.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.IDbeing 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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