SELECT… CONNECT BY

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);

Add Sample data.sql

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 concatenation operator (||) may 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:

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


© Copyright SS64.com 1999-2014
Some rights reserved