Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

February 15, 2022

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Description

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
Catch up on the recording for our last session!
Don't miss our next live session!
This session: https://asktom.oracle.com/pls/apex/asktom.search?oh=16685

Session Resources

Your Experts

  • #SELECTION#
    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
    #MISC#
    #ACTIONS#

All Sessions

Resources