Hierarchical Queries

Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.

Tree walking enables you to construct a hierarchical tree if the relationship lie in the same table. For instance, a manager column which exists in the emp table which defines the managerial hierarchy.

• START WITH specifies the root row(s) of the hierarchy.
• CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row.
• LEVEL (pseudocolumn) returns 1 for a root row, 2 for a child of a root, and so on for each row returned by a hierarchical query.

Refer: http://www.orafaq.com/node/2038