Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux