Search Postgresql Archives

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

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

 



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