Hierarchical Queries : Hierarchical Queries « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Hierarchical Queries

Using the CONNECT BY and START WITH Clauses

The syntax for the CONNECT BY and START WITH clauses of a SELECT statement is as follows:

SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

where

1. LEVEL is a pseudo-column that tells you how far into a tree you are.
2. LEVEL returns 1 for a root node, 2 for a child of the root, and so on.
3. start_condition specifies where to start the hierarchical query from.
4. You must specify a START WITH clause when writing a hierarchical query.
5. An example start_condition is employee_id = 1, which specifies the query starts from employee #1.
6. prior_condition specifies the relationship between the parent and child rows.
7. You must specify a CONNECT BY PRIOR clause when writing a hierarchical query.

An example prior_condition is employee_id = manager_id, which specifies the relationship is between the parent employee_id and the child manager_id-that is, the child's manager_id points to the parent's employee_id.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813