Select data with a hierarchical (parent/child) relationship. (also used by EXPLAIN PLAN)
Syntax:
SELECT…
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]
Key:
START WITH : The row(s) to be used as the root of the hierarchy
CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy
NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)
ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Examples
First create a table with some data: (A sample list of files and folders similar to the Windows Start menu)
CREATE TABLE stmenu (
id int,
Menuitem varchar2(75),
parentid int);
Display the folder and parent ID (Walk up the tree showing all items):
COLUMN id FORMAT 999
COLUMN Menuitem FORMAT a84
COLUMN parentid FORMAT 999
COLUMN parentfolder FORMAT a84
COLUMN x FORMAT a85
< SELECT id, Menuitem, parentid
FROM stmenu
CONNECT BY PRIOR id=parentid
START WITH id=1;
ID Menuitem PARENTID ---- -------------------------------------------- -------- 1 \Start Menu\Programs\ 2 Startup\ 1 3 Administrative Tools\ 1 18 Authorization Manager.lnk 3 19 Certification Authority.lnk 3 …
Display Item and it's immediate Parent
SELECT
(select Menuitem
from stmenu
where id=x.parentid
) parentfolder,
Menuitem
FROM stmenu x
CONNECT BY PRIOR id=parentid
START WITH id=1;
Walk down the tree (show heirarchy for one item)
COLUMN Menuitem FORMAT a34
SELECT id, Menuitem,parentid
FROM stmenu
CONNECT BY PRIOR parentid=id
START WITH id=29;
Walk up the tree for 3 items
SELECT id, Menuitem,parentid
FROM stmenu
where id in (4,12,28)
CONNECT BY PRIOR id=parentid
START WITH id=1;
Display the full tree (indenting child items)
SELECT
lpad(' ',2*(parentid-1))|| Menuitem
FROM stmenu
CONNECT BY PRIOR id=parentid
START WITH id=1;
Operators
PRIOR - Most commonly used when comparing column values with the equality operator. PRIOR identifies the parent row in the column.
The PRIOR keyword can be on either side of the = operator. CONNECT BY PRIOR id=parentid will return different results to CONNECT BY PRIOR parentid=id
Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, this can result in an infinite loop through the possible combinations.
CONNECT_BY_ROOT - When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. (Oracle 10g)
Pseudo-columns
LEVEL - Returns a number indicating the level in the heirarchy: 1 for a root row, 2 for a child of a root, and so on.
New pseudo-columns in Oracle 10g:
CONNECT_BY_ISCYCLE - Returns 1 if the current row has a child which is also its ancestor (otherwise 0.)
CONNECT_BY_ISLEAF - Returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition (otherwise 0.) Indicates that a row can be further expanded.
Notes:
Precedence of the concatenation operator (||) might not be what you expect.
CONNECT BY PRIOR C1 || C2 =…
is equivalent to
CONNECT BY (PRIOR C1) || C2 =…
so use
CONNECT BY PRIOR (C1 || C2) =…
"If future generations are to remember us with gratitude rather than contempt, we must leave them more than the miracles of technology. We must leave them a glimpse of the world as it was in the beginning, not just after we got through with it." ~ President Lyndon B. Johnson (The Wilderness Act, 1964)
Related Oracle Commands:
EXPLAIN PLAN
UNION
Analytic Features (for Oracle 8i and above)
Lag and Lead - functions to access other rows without the need to perform a self join.
Select Hints
SQL Examples - Oracle Co-Operative FAQ
Outer Join examples - ADP - Analyse, Design & Programmierung GmbH
Related Views:
DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES
DBA_TABLES ALL_TABLES USER_TABLES TAB
DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_TAB_COLUMNS
DBA_TAB_COLS ALL_TAB_COLS USER_TAB_COLS
DBA_VIEWS ALL_VIEWS USER_VIEWS
DICTIONARY
DICT_COLUMNS