Oracle start with. connect by prior clause for Recursive queries

The select statement in Oracle can use the start with... connect by prior clause to implement recursive queries. The connect by clause is used in structured queries. Its basic syntax is as follows:

Select... from <TableName>
Where <Conditional-1>
Start with <Conditional-2>
Connect by <Conditional-3>;

<Conditional-1>: Filter condition used to filter all returned records.
<Conditional-2>: Conditions for restarting the root node in the query result.
<Conditional-3>: Connection Condition

The data organization structure is as follows:

 

 

 

The database table structure is as follows:

Create table t2 (
Root_id number,
Id number,
Name varchar (5 ),
Description varchar (10)
);

Insert into t2 (root_id, id, name, description) values (0, 1, 'A', 'aaa ');
Insert into t2 (root_id, id, name, description) values (1, 2, 'a1', 'aaa1 ');
Insert into t2 (root_id, id, name, description) values (1, 3, 'a2 ', 'aaa2 ');
Insert into t2 (root_id, id, name, description) values (0, 4, 'B', 'bbb ');
Insert into t2 (root_id, id, name, description) values (, 'b1 ', 'bbb1 ');
Insert into t2 (root_id, id, name, description) values (, 'b2', 'bbb ');

Obtain the complete tree:

Select * from t2 start with root_id = 0 connect by prior id = root_id;
 

 

 

Get the child tree:

Select * from t2 start with id = 1 connect by prior id = root_id;
 

 

 

Select * from t2 start with id = 4 connect by prior id = root_id;
 

 

 

If prior in connect by prior is omitted, deep recursion is not performed for the query.
For example:

Select * from t2 start with root_id = 0 connect by id = root_id;
 

 

 

Select * from t2 start with id = 1 connect by id = root_id;
For example: