I’ve come across a very neat trick in Oracle that I thought I’d share. Its useful for situations where you can have circular references in the data diagram, for example when the table includes a column which is a reference for another entry in the same table. Usually it would be necessary to write some function which could be called recursively and allow you to traverse this tree layout, however in Oracle there is the CONNECT BY PRIOR syntax.

The users and groups example

Sticking with my earlier example let’s say I’ve got an employees table and the employee can have a manager, which is an employee

users

select first_name || ' ' || last_name, level
from users
connect by prior id = manager
start with id = 1

Will output the list of people followed by their reports. We can distinguish who is where in the tree using the magical column “level” which is available when we connect in this way. I’ve used the level to indent the name column by two spaces for each level down the tree the person is and to show their job title:

select lpad('  ', (level - 1) * 14) || first_name || ' ' || last_name, job_title
from users
connect by prior id = manager
start with id = 1