Re: Subselects - recursion problem

On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote:
> Martijn,
>
>
> On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> > "recursion problem" ? It's called a correlated subquery. SQL is
> > declarative, you state what you want and the database figures out how
> > to get the answer for you. I think you need to go and read up on the
> > basics of SQL.
>
> Declarative or not, it looks strange having the output of the first
> select dependent on a second select, which is dependent on the output of
> the first select . .

Not really, if you think about it. SQL is a form of relational algebra
and like in normal algebra it's not unusual to have various variables
relating to eachother in various ways. Some expressions may look
recursive, but that's just another relationship.

As you can see from the query plan, it scans through each record in c1
filtering out rows based on the easy conditions. It then works out the
subquery for each row and compares the result with policy. If it works
the row is returned otherwise it keeps going. There's no recursion.

Perhaps the easiest way to think about it is having the subquery as a
function, and write it like:

SELECT c1.loc, c1.lob, c1.policy
FROM crec AS c1
WHERE c1.t_type = '1'
AND c1.t_diss = '2'
AND c1.recon = 'Y'
AND c1.policy = Subquery( c1 )
ORDER BY c1.loc, c1.lob, c1.policy ;

That doesn't look recursive to me, yet it's the same thing...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.