Thursday, 30 August 2007

Loops - wtf

Ponder this for a moment (on Oracle 9i):

begin
    for r_x in (select * from x)
    loop
        for r_y in (select * from y where x_id = r_x.id)
        loop
            for r_z in (select * from z where y_id = r_y.id)
            loop
                -- some code ...
            end loop;
        end loop;
    end loop;
end;
Thinking that it was too obvious, I spent ten confused minutes wondering what could've been the idea behind it. When I inquired with the creator, the succinct "I thought this was more readable code" landed me back in reality.

In case you're wondering, I prefer:

select  * 
from    x
join    y on x.id = y.x_id
join    z on y.id = z.y_id;

2 comments:

Carolyn said...

seems to me you mean

select *
from x
join y on x.id = y.x_id
join z on y.id = z.y_id;

i rather prefer

select *
from x, y, z
where x.id = y.x_id
and y.id = z.y_id;

thanks for your comments on the SQL Expert beta exam...

JochenVdV said...

Carolyn, Thanks for the correction. You can read here why I like to use the ANSI Syntax.