Make delicious recipes!

Connect By

‘Connect By’ can be used in SQL to read/update hierarchical data in a table.

It has the following syntax:

connect_by_clause:=

[ Start With (condition1) ] Connect By [ NoCycle ] (condition2)


An example for condition2 above can be:

Connect by prior employee_id = manager_id and

prior manager_id = director_id


Example:


To see the effect of ‘prior’ keyword, let us just reverse the application of ‘prior’ from emp_id to mgr_id


So, prior just helps to determine which row is treated as parent by the DB.


Example to show usage of ‘Start With’:



‘Connect by’ can be made to work on multiple tables too (in case parent child relationship is split across 2 tables) by first joining the 2 tables and then running the connect by.




Like us on Facebook to remain in touch
with the latest in technology and tutorials!


Got a thought to share or found a
bug in the code?
We'd love to hear from you:

Name:
Email: (Your email is not shared with anybody)
Comment:

Facebook comments:

Site Owner: Sachin Goyal