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

[@njwarnock]

Absolutely excellent tutorial - thanks so much!

[@drodsou]

Amazing, decades using SQL and never heard of this :-D. Thanks!

[@omarmorales909]

I been looking for an easy explanation on this subject and finally found your video! Thanks for sharing this, it has helped me to resolve a really big issue here at the manufacturing plant where I work. Thanks again!

[@siddhartharao8490]

Great thanks for explaining the concept Precisely.

[@snaidu70]

Thank you Chris for this awesome explanation

[@albertogonzalez5114]

Thanks so much for this useful and enlightening explanation

[@praveenkumar-rk3hp]

Thank you Chris, This was very helpful
But both connect by & recursive with are running for so long time (more than 1 hr) for data set of around 7M
Can you please provide me any alternate

[@jayashri_since2001]

Thankyou

[@iftikhar8508]

How to retrieve a tree in forest like what if there are multiple hierarchies? for example a table with data about countries and their president, prime minister and ministers. Here every country will have its own Hierarchy and hence more than one base cases.

[@cesarcastillo2643]

in Oracle I tried both and I cannot pass from the first level down. I used the same recursive statement in sql lite and works as expected.

[@Pragya1819]

Hi...
I'm having a BOM components under which many assembly n sub assembly are there.. I have transferred some of the assembly to manufacturing now, I need to fetch the which all assemblies are left (showing all child grandchild ) in oracle.. Plz help.. It's urgent

[@CaboLabsHealthInformatics]

are these clauses standard or Oracle only?

[@nadinfridburg7088]

Thank you!!!!!!!

[@SharjeelTahir-zq5ps]

bad mic