Using Connect By in Oracle

When dealing with hierarchical data, Oracle has a Connect By clause that is very useful. I will try and demonstrate through examples in the following sections.

Hierarchical Data

In this example, our data will be structured in a tree-structure. Our example data will be a folder-structure.

 - Root Folder
    - Development
        - 3rd Party Software
        - Licenses
        - Release Docs
    - Sales
        - Clients
            - ChainsawsRus
            - Knitting Fever
        - Presentations

In a database, this folder structure could be represented by

TABLENAME: Folders
-------------------------------------------------|
| FolderID | ParentFolderID | FolderName         | 
-------------------------------------------------|
| 1        | 0              | Root Folder        |
| 2        | 1              | Development        |
| 3        | 2              | 3rd Party Software |
| 4        | 2              | Licenses           |
| 5        | 2              | Release Docs       |
| 6        | 1              | Sales              |
| 7        | 6              | Clients            |
| 8        | 7              | ChainsawsRus       |
| 9        | 7              | Knitting Fever     |
| 10       | 6              | Presentations      |
-------------------------------------------------|

This data example will serve as the primary example through the rest of the article.

Connect By

Let us just jump right into it and make a query on that data.

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 0 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

This returns the following result:

Oracle Connect By Example 01

Let us go through the SQL.

Level
The first line references the “Level” pseudo column. Whenever a hierarchical query is performed, this pseudo column as added to the result. The level column contains a number describing the level of the row in the query.

START WITH
The START WITH clause tells oracle how to define the root elements. If we had written:

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 7 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

The it would only return: Knitting Fever and ChainsawsRus

CONNECT BY and PRIOR
The CONNECT BY clause defines how to make the structure in the hierarchy. The PRIOR keyword, simply put, tells Oracle what the column should be compared to the parent in the hierarchy. If we instead had executed the following SQL:

SELECT Level, F.* FROM Folders F 
START WITH FolderID = 7 
CONNECT BY FOLDERID = PRIOR PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

It would instead consider the root folder as the one with FolderID 7 (Clients) and because the PRIOR keyword was moved in front on PARENTFOLDERID instead, it reverses the query, giving us the following result:

Oracle Connect By Example 02

ORDER SIBLINGS BY
This tells Oracle how to order siblings on the same level. For the purpose of the example I made it descending as it can be seen from the result.

I hope this article has helped someone understand oracle hierarchical queries. For more information, consule the Oracle Database SQL Reference.