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

[drodsou]

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

[njwarnock]

Absolutely excellent tutorial - thanks so much!

[omarmorales]

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!

[siddhartharao]

Great thanks for explaining the concept Precisely.

[snaidu]

Thank you Chris for this awesome explanation

[albertogonzalez]

Thanks so much for this useful and enlightening explanation

[iftikhar]

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.

[praveenkumar-rkhp]

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

[cesarcastillo]

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.

[Pragya]

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?