Thursday, April 6

SQL - Connect by

Lots of people has been asking me about self joins and connect by queries in the last week.
Not sure, guess its just that time of the year. Anyway.
One question was regarding how to find parent relations in a "people-table". It's easy to find someones parent with a self join if the table is designed correctly, but what if you want to find the grand parent or even great grant parents? You can't really self-join the table N times.
Here is where connect by comes it. Learn how to use connect by and how to take advantage of the pseudo column level in your application. Level tells you the "depth" in the relation tree, how many parents you have. Perfect to render a tree structure.

Look at this sample table:
create table cb (
id number(4),
name varchar2(20),
age number(3),
parent number(4)
);

create sequence cb_id_seq;

-- Some sample data
insert into cb values(cb_id_seq.nextval, 'Adam', 99, null);
insert into cb values(cb_id_seq.nextval, 'Scott', 75, 1);
insert into cb values(cb_id_seq.nextval, 'Tiger', 74, 1);
insert into cb values(cb_id_seq.nextval, 'Puffer', 74, 1);
insert into cb values(cb_id_seq.nextval, 'Sarah', 54, 3);
insert into cb values(cb_id_seq.nextval, 'Bob', 54, 3);
insert into cb values(cb_id_seq.nextval, 'Greg', 25, 4);
insert into cb values(cb_id_seq.nextval, 'Mary', 34, 6);

-- So what do we have now.
select * from cb;

ID NAME AGE PARENT
---- -------------------- ---- ----------
1 Adam 99
2 Scott 75 1
3 Tiger 74 1
4 Puffer 74 1
5 Sarah 54 3
6 Bob 54 3
7 Mary 34 6
8 Greg 25 4


-- Ok, lets use the nice level pseduo-column for something usefull.
column LVL format a30
select lpad(' ',3*level)||name lvl ,age from cb
start with id=1 connect by prior id=parent;

LVL AGE
------------------------------ ----
Adam 99
Scott 75
Tiger 74
Sarah 54
Bob 54
Mary 34
Puffer 74
Greg 25


Oracle provides good docs as usual.

2 comments:

The Shaolin said...
This comment has been removed by the author.
The Shaolin said...

This was a very helpful post. I was struggling with 'connect by' for quiet some time now. The example you chose was the best way to explain the recursive behavior. Thanks.