SQL hierarchies using CONNECT BY and recursive WITH

You can traverse data trees in Oracle Database using

- CONNECT BY
- recursive WITH

This session explains how these work. Highlights include:

01:30 - The WRONG way to build a hierarchy in SQL
05:50 - Use cases for CONNECT BY/recursive WITH
13:30 - Terminology parent/child relationships, roots, and levels
15:40 - CONNECT BY basics
18:40 - Recursive WITH basics
20:00 - How many roots can a query have?
22:45 - Demo of CONNECT BY and recursive WITH; identifying roots and parent-child relationship, adding level
31:30 - Sorting hierarchies depth-first vs breadth-first search
35:00 - How to sort trees using CONNECT BY and identify leaves
38:00 - How to sort trees using recursive WITH and identify leaves
41:45 - Demo of sorting hierarchies
50:20 - Dealing with loops in hierarchies
51:30 - Detecting loops with CONNECT BY
52:00 - Detecting loops with recursive WITH
53:10 - Demo of loop detection methods
57:00 - Summary

• #science_technology
• #oracle_cloud
• #oracle_developers
• #oracle_database