Hierarchical query in Oracle

Hierarchical query Concept

Syntax format:

Select [level], column, expr... from table

[Where condition]

Start with Condition

Connect by [prior column1 = column2 |

Column1 = prior column2];

Hierarchical query is identified by the start with and connect by clauses:

1. The level keyword is optional, indicating the level, 1 indicating the root, 2 indicating the root child, and other identical rules.

2. A table can be followed by a from statement, but a view can only be a table.

3. The where condition limits the rows returned by the query, but does not affect the hierarchy. It truncates the node, but the lower child of the truncated node is not affected.

4. Start with indicates the Start Node. This clause is required for a real hierarchical relationship, but not required.

5. Connect by prior specifies the parent-child relationship. The prior location does not have to be after connect by. This is also required for a real hierarchy.

Example: in a self-join, a Cartesian set occurs if the data volume is large.

SQL> -- treats the same table as multiple tables using the table alias

SQL> select e. ename | 'The Boss is '| B. ename

2 from emp e, EMP B

3 where E. Mgr = B. empno;

Result:

In order to avoid this situation, the hierarchical query in Oracle is used. In the following example, a tree graph is shown in this figure.

Hierarchical query example:

SQL> -- execute hierarchical Query

SQL> select level, empno, ename, Mgr

2 from EMP

3 connect by prior empno = Mgr

4 start with Mgr is null

5 order by 1;

Result: