SQL recursive query on self referencing table (Oracle)

Question

Lets assume I have this sample data:

| Name     | ID | PARENT_ID |
-----------------------------
| a1       | 1  | null      |
| b2       | 2  | null      |
| c3       | 3  | null      |
| a1.d4    | 4  | 1         |
| a1.e5    | 5  | 1         |
| a1.d4.f6 | 6  | 4         |
| a1.d4.g7 | 7  | 4         |
| a1.e5.h8 | 8  | 5         |
| a2.i9    | 9  | 2         |
| a2.i9.j10| 10 | 9         |

I would like to select all records start from accountId = 1, so the expected result would be:

| Name     | ID | PARENT_NAME | PARENT_ID | 
-------------------------------------------
| a1       | 1  | null        | null      |
| a1.d4    | 4  | a1          | 1         |
| a1.e5    | 5  | a1          | 1         |
| a1.d4.f6 | 6  | a1.d4       | 4         |
| a1.d4.g7 | 7  | a1.d4       | 4         |
| a1.e5.h8 | 8  | a1.e5       | 5         |

I am currently able to make the recursive select, but then I can't access the data from the parent reference, hence I can't return parent_name. The code I'm using is (adapted to the simplistic example):

SELECT id, parent_id, name
FROM tbl 
  START WITH id = 1 
  CONNECT BY PRIOR id = parent_id

What SQL should I be using to the mentioned above retrieval?

Additional key words for future seekers: SQL to select hierarchical data represented by parent keys in same table

Call me stubborn, but I'm still not convinced that the accepted answer it's the best performant. Could you post how suggested query's perform on your data? — Samuel
– Samuel, Commented Feb 23, 2010 at 17:43
@Samuel I have accepted OMG answer because of it's simplicity and for it's fit for this scenario requirements. I am still not convinced that subquries perform better then joins: based on @OMG comment regarding tkprof (I \\assume\\ he did run the tests) I think it's safe to estimate that the proposed solution is the correct one. I currently do not have enough test data to produce meaningful results (<50 records). It's perfectly fine that you do not accept that your answer is not the correct one. I don't think you should. I should mention that I am in no way an Oracle DBA expert. — Maxim Veksler
– Maxim Veksler, Commented Feb 23, 2010 at 18:00

OMG Ponies · Accepted Answer · 2010-02-23 16:36:49Z

37

Use:

    SELECT t1.id, 
           t1.parent_id, 
           t1.name,
           t2.name AS parent_name,
           t2.id AS parent_id
      FROM tbl t1
 LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1 
CONNECT BY PRIOR t1.id = t1.parent_id

edited Feb 23, 2010 at 16:36

answered Feb 23, 2010 at 15:41

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Samuel Over a year ago

I think that query is doing unnecessary work joining with itself too early. Don't you think?

OMG Ponies Over a year ago

The OP lists columns that should return NULL, which the LEFT JOIN does - which is not unnecessary work either.

Maxim Veksler Over a year ago

VERY GOOD, I'm happy. Thank you :). Please note for current version you would get "SQL Error: ORA-00918: column ambiguously defined", to fix this the SQL needs to be tweaked to read: START WITH t1.id = 1 CONNECT BY PRIOR t1.id = t1.parent_id

OMG Ponies Over a year ago

@Maxim: Yeah, I realized after the 5 min mark that I missed the table aliases. Corrected.

Tomáš Zato Over a year ago

What happens if there's a circle in the graph? Will the SELECT fail, or will it loop forever?

|

ari · Accepted Answer · 2012-08-12 09:50:26Z

16

What about using PRIOR,

so

SELECT id, parent_id, PRIOR name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id`

or if you want to get the root name

SELECT id, parent_id, CONNECT_BY_ROOT name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id

answered Aug 12, 2012 at 9:50

ari

1811 silver badge5 bronze badges

Comments

PaulMurrayCbr · Accepted Answer · 2012-07-30 05:45:37Z

11

Using the new nested query syntax

with q(name, id, parent_id, parent_name) as (
    select 
      t1.name, t1.id, 
      null as parent_id, null as parent_name 
    from t1
    where t1.id = 1
  union all
    select 
      t1.name, t1.id, 
      q.id as parent_id, q.name as parent_name 
    from t1, q
    where t1.parent_id = q.id
)
select * from q

answered Jul 30, 2012 at 5:45

PaulMurrayCbr

1,27013 silver badges17 bronze badges

Comments

Samuel · Accepted Answer · 2010-02-23 16:07:29Z

2

Do you want to do this?

SELECT id, parent_id, name, 
 (select Name from tbl where id = t.parent_id) parent_name
FROM tbl t start with id = 1 CONNECT BY PRIOR id = parent_id

Edit Another option based on OMG's one (but I think that will perform equally):

select 
           t1.id, 
           t1.parent_id, 
           t1.name,
           t2.name AS parent_name,
           t2.id AS parent_id
from 
    (select id, parent_id, name
    from tbl
    start with id = 1 
    connect by prior id = parent_id) t1
    left join
    tbl t2 on t2.id = t1.parent_id

edited Feb 23, 2010 at 16:07

answered Feb 23, 2010 at 15:34

Samuel

2,5002 gold badges19 silver badges21 bronze badges

6 Comments

Maxim Veksler Over a year ago

YES, but as far as I understand this is inefficient because that causes the RDBMS to make additional inner query for each returned result?

OMG Ponies Over a year ago

@Maxim: You are correct - that is a correlated subquery, that will execute once for every row returned. While it works, it is the least efficient means available.

Samuel Over a year ago

@OMG I don't think so. The optimizer is clever enough to infer a join by itself. Look at the explain plan.

Maxim Veksler Over a year ago

@Samuel: Can you please explain your rational for using the inner select in this situation?

Samuel Over a year ago

@OMG Sorry but I can't agree. Scalar subquerys are a valid alternative to outer join's, and usually perform faster. Look at this oratechinfo.co.uk/scalar_subqueries.html

|

Allan · Accepted Answer · 2010-02-25 23:01:33Z

It's a little on the cumbersome side, but I believe this should work (without the extra join). This assumes that you can choose a character that will never appear in the field in question, to act as a separator.

You can do it without nesting the select, but I find this a little cleaner that having four references to SYS_CONNECT_BY_PATH.

select id, 
       parent_id, 
       case 
         when lvl <> 1 
         then substr(name_path,
                     instr(name_path,'|',1,lvl-1)+1,
                     instr(name_path,'|',1,lvl)
                      -instr(name_path,'|',1,lvl-1)-1) 
         end as name 
from (
  SELECT id, parent_id, sys_connect_by_path(name,'|') as name_path, level as lvl
  FROM tbl 
  START WITH id = 1 
  CONNECT BY PRIOR id = parent_id)

Collectives™ on Stack Overflow

SQL recursive query on self referencing table (Oracle)

5 Answers 5

6 Comments

Comments

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related