Oracle SQL looped self-join

Question

Context:
Let's say that I have table that has a FOREIGN KEY which references its own PRIMARY KEY, like this:

|---------------------|------------------|------------------|
|          ID         |       NAME       |     PARENT_ID    |
|---------------------|------------------|------------------|
|          01         |       John       |         04       |
|---------------------|------------------|------------------|
|          02         |       Paul       |         01       |
|---------------------|------------------|------------------|
|          03         |       George     |         02       |
|---------------------|------------------|------------------|
|          04         |       Ringo      |         03       |
|---------------------|------------------|------------------|

Problem:
So as you see there is looped hierarchy: Ringo->George->Paul->John->Ringo->George->Paul->John->etc.

Question:
Whether there is a SQL select that can detect such loops?

I know that I can write recursive PL/SQL procedure but I prefer solution with "pure" SQL.

Thank you in advance

This is a faq. (Obviously.) Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 03 '20 at 11:16

score 3 · Accepted Answer · answered Apr 03 '20 at 09:28

You can use a CONNECT BY query with the CONNECT_BY_ISCYCLE pseudo-column to look for loops - see example from Oracle docs:

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle"
FROM employees
WHERE level <= 3 
AND   department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

score 1 · Answer 2 · answered Apr 03 '20 at 09:44

You can do this with connect by nocycle and connect_by_iscycle. For your table structure, that would look like:

select id, name, parent_id, connect_by_iscycle
from mytable
connect by nocycle id = prior parent_id
start with id = 4

connect by nocycle causes the query to stop iterating when a cyle is met, and pseudo-column connect_by_iscycle contains a flag that indicates at which point it happened, as shown in this demo:

ID | NAME   | PARENT_ID | CONNECT_BY_ISCYCLE
-: | :----- | --------: | -----------------:
 4 | Ringo  |         3 |                  0
 3 | George |         2 |                  0
 2 | Paul   |         1 |                  0
 1 | John   |         4 |                  1  --> cycle detected here

Thorsten Kettner · Answer 3 · 2020-04-03T11:15:36.197

Here is a solution with a recursive cte:

with cte (id, parent_id, ids) as
(
  select id, parent_id, to_char(id) from mytable
  union all
  select t.id, t.parent_id, ids || ' -> ' || t.id
  from cte
  join mytable t on t.id = cte.parent_id
)
cylce id set cycle to 1 default 0
select ids as cycling_ids
from cte
where cycle = 1
order by ids;

Result:

+ ----------------------+
| CYCLING_IDS           |
+ ----------------------+
| 1 -> 4 -> 3 -> 2 -> 1 |
| 2 -> 1 -> 4 -> 3 -> 2 |
| 3 -> 2 -> 1 -> 4 -> 3 |
| 4 -> 3 -> 2 -> 1 -> 4 |
+ ----------------------+

If you want to see each cycle just once (which I assume), remember the minimum ID per cycle and show only one cycle per minimum ID:

with cte (id, parent_id, ids, min_id) as
(
  select id, parent_id, to_char(id), id from mytable
  union all
  select t.id, t.parent_id, ids || ' -> ' || t.id, least(t.id, cte.min_id)
  from cte
  join mytable t on t.id = cte.parent_id
)
cycle id set cycle to 1 default 0
select min(ids) as cycling_ids
from cte
where cycle = 1
group by min_id
order by min_id;

Result:

+ ----------------------+
| CYCLING_IDS           |
+ ----------------------+
| 1 -> 4 -> 3 -> 2 -> 1 |
+ ----------------------+

Demo with more IDs and different cases: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f7f924cd8759d67a188b7c11f2d071ef

(This is still not perfect. If a very small ID leads to higher IDs forming a cycle, e.g. if we inserted an ID 0 referring ID 3 as a parent too, the query would show the cycle more than once. This is not easy to avoid, as we would have to detect the minimum ID within the circle. I would probably write a small PL/SQL function to get this minimum ID from the IDs string.)

Oracle SQL looped self-join

3 Answers3