Oracle's start with ... connect by ...

Grammar:

SELECT * FROM Some_table

[Where Condition 1]

Connect by [Condition 2]

Start with [condition 3];

The order in which the connect by and start with statements is placed does not affect the results of the query, [WHERE Condition 1] may not be required.

[WHERE Condition 1], [Condition 2], [condition 3] each have different scopes:

[Where Condition 1]

is filtered according to the record selected in the "Connect by [Condition 2] start with [condition 3]", which is filtered for a single record and does not take into account the structure of the tree ( final filtering );

[Connect by condition 2]

Specify the conditions of the construction tree, as well as the filtering criteria for the tree branch, where the filtering performed will filter out the qualifying records and all the sub-nodes under it;

[Start with condition 3]

Qualifying as the search starting point condition, if the top-down search is qualified as the root node of the condition, if the bottom-up search is limited as a leaf node conditions;

To determine the starting node depending on whether connect is from top to bottom or bottom to top, either a leaf node or a parent node, these start nodes can be multiple and contain these nodes.

==========================================================================

Example:

Table structure:

-- Create Table Create Table A (  number,  VARCHAR2),  number )

SELECT * from a start with id=2 connect by prior id=pid;--from parent to child (top to bottom) SELECT * from a start with id=2 connect by prior PI d= ID;   --From child to parent (bottom to top) SELECT * from A start with id=2 connect by pid= ID; -No loops
 

The understanding of prior

1.prior No, no, you can only find the records that meet the "start with [condition 3]" and not the child nodes or parent nodes that are looking for these records.

2.

Connect by Prior Id=pid uses a top-down search method (find the parent node first and then find the child node)

The Connect by Id=prior PID uses a bottom-up search method (find the leaf node first and look for the parent node)

From a child node to a parent node, prior refers to a child node that constructs a relationship from a child node to a parent node, that is, the ID of the child node's pid= parent node (prior Pid=id), and from the parent node to the child node, prior refers to the parent node, and constructs a relationship from the parent node to the child node, the ID = pid of the child node (proid ID =pid).

The 3.prior id=pid and pid=prior ID effects are the same.

Oracle's start with ... connect by ...