Hierarchical SQL Queries: Databases for Developers #16

Often data contains a parent-child relationship between rows in the same table. For example, company org charts and family trees.
You can use the power of SQL to traverse these hierarchical relationships. There are two ways to do this in Oracle Database:
Connect By
Recursive With
Watch this video to learn about data trees and find out how these work!
Further reading:
Hierarchical queries in the docs: docs.oracle.co...
Recursive subquery factoring in 11.2: oracle-base.co...
Take the course! devgym.oracle....
Need help with SQL? Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.c...
============================
The Magic of SQL with Chris Saxon
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@edgards]

Fantastic video!! it's 8pm here at my office and I'm dancing because this function has helped me a lot.

[@TheMagicofSQL]

Awesome :) Got a video of you dancing? ;)

[@shahnawazabdulrehman526]

​@@TheMagicofSQLunfortunately no dance video from him😢

[@Samalbabu1]

No where on web I found such helpful fundamental description on hierarchy..!! Thank you 😊

[@TheMagicofSQL]

Thanks! Glad this helped you :)

[@cavin3401]

Snowden is now teaching SQL seems FUN

[@phamucmanh1750]

So amazing ! Keep producing videos, please. Thank you !

[@jaiwr9460]

i love the effort

[@CalvinYio]

just made reading a while wall of text easier but is there any tutorial or exercise i can practise this with?

[@TheMagicofSQL]

You can find an interactive tutorial at livesql.oracle.com/apex/livesql/file/tutorial_GQMLEEPG5ARVSIFGQRD3SES92.html

[@shashankjain2270]

Great Video!, Helped me understand the concept .

[@TheMagicofSQL]

You're welcome, glad you found this useful

[@mohammadalijavaheri771]

thank you for this video

[@mario17-t34]

Thanks much, So this is for Oracle, I think something similar can be done on MS? (and without music-) Thanks much

[@TheMagicofSQL]

Connect by is specific to Oracle Database; I imagine recursive with works on MS, but I don't know the details You have to listen to music whichever database you use ;)

[@jallunick1032]

Where can I find the sql queries shown in this video?

[@TheMagicofSQL]

You can join the courses at devgym.oracle.com/pls/apex/dg/class/databases-for-developers-foundations.html devgym.oracle.com/pls/apex/dg/class/databases-for-developers-next-level.html

[@aggreyafray6869]

hi just inquiry can i use those functions in mysql and php?

[@TheMagicofSQL]

MySQL does support recursive with/CTEs. I'm not sure if/how it covers all the features discussed here though.

[@DavidLeiser88]

What to do if you dont know the root? Can this be done in the same statement or should this be done in a seperate querry?

[@TheMagicofSQL]

You can use a subquery to identify the root row(s). This works for connect by (start with col in ( select ... ) ) and the base query in recursive with.

[@AndiRadyKurniawan]

Is the hierachical query faster than the nested set model?

[@TheMagicofSQL]

I'm not familiar with the nested set model - could you clarify what this is?

[@AndiRadyKurniawan]

@@TheMagicofSQL it's a way to manage hierarchical data where a separate table is used to store the boundaries of the node in the tree. You perform the query on this separate table instead of the actual table. See en.wikipedia.org/wiki/Nested_set_model

[@TheMagicofSQL]

Gotcha. It really depends on the query you're running. If you need to traverse the tree, I suspect connect by/recursive with will be faster. It really comes down to the data and your query though

[@bostonmacosx]

Ok...why in the recursive with do you have a "yf." there is no alias refereing to a yf table...

[@TheMagicofSQL]

You mean here? kzbin.info/www/bejne/pnPCn4J_oJigo7s It's on the join to your_family yf in the second block of the union all

[@bostonmacosx]

@@TheMagicofSQL Yeah it was a bit off the screen..but I see it now.... My only question is can you look up the tree as well as down so if I select a middle of hte road tree item can I still see the whole tree or do I always need to start at the root..

[@TheMagicofSQL]

You can go up or down the tree, just flip the tables in the join criteria. If you're asking if you can go up AND down in one query... yes it's possible. You need to do both the joins in both directions and have cycle detection. Here's a connect by example: with rws as ( select level x, lag ( level ) over ( order by level ) y from dual connect by level

[@jeandedieuntirampeba8817]

WHAT CAN I DO WITH MYSQL WHEN I WANT TO RESOLVE THE PROBLEM LIKE THAT?

[@TheMagicofSQL]

I focus on Oracle Database here, so I'm not sure; sorry!

[@ashutoshshrivastava9663]

What about Binary.

[@neeleshshah]

Oh.. to take care of divorces, re marriages, children from multiple marriages.. and… shocker.. marriages of cousins (at a broad 4th/5th level).. i had to patch up the data model.. !!

[@TheMagicofSQL]

Yeah, family trees can be tricky!

[@atulgupta9301]

Not able to understand the syntax.

[@TheMagicofSQL]

What exactly are you struggling with?

[@samt1705]

The background music needs to be changed.

[@dummuvikash4957]

[@wiktor1983]

"But, of course, in real life everyone has two parents" In 2022 is not so obvious...

[@KirillBezzubkine]

Video s good but this damn stupid BG music is really distracting

[@JustBCWi]

Thumbed down, misleading title. This is "Oracle Databases" for developers

[@enockoloo3814]

too much illustration is distracting and annoying.

[@TheMagicofSQL]

What specifically is distracting you?