Introduction to connect by usage in Oracle

To solve the problem that the self-connected query in Oracle is not suitable for manipulating large tables, the Connect by method is implemented. Oracle can be used with the start with ... The Connect by prior clause implements recursive queries, and connect by is applied in structured queries.

Basic syntax:

Select ... from <TableName>
where <Conditional1>
Connect by <Conditional2>

Start with <Conditional3>

The order of connect by and start with can be swapped

which

Conditional1 is a filter condition that is used to filter all records returned.
Conditional2 is the connection condition

Conditional3 is a filter condition that is used to filter all records returned.

SELECT * from EMP; where EMPNO (employee number), MGR (manager number)

The corresponding relationship between the employee and the manager can be organized as follows: where the manager number of the next level node is the employee number at the previous level

Query statement:

Sql> Select Level series, connect_by_isleaf whether Leaf, ename employee name, MGR Manager from EMP Connect by prior empno=mgr start with Mgr is null;

Level,connect_by_isleaf,connect_by_iscycle Pseudo-Column

Level is where this data belongs.

Connect_by_isleaf is the leaf node of the tree.

Connect_by_iscycle The branch that caused the death cycle to occur.

The prior operator must precede one of the two columns of a connection relationship. For parent-child relationships between nodes, the prior operator represents the parent node on one side and the child node on the other side, determining whether the find tree structure is in the order of top-down or bottom-up

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Introduction to connect by usage in Oracle