Hierarchical Data

CONNECT_BY_ISLEAF

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition, 0 otherwise.

select
  lpad(' ', level-1 ) || key as key_indented ,
  CONNECT_BY_ISLEAF
from
  t
start with
  key = 'server'
connect by
  parent_key = prior key
;
 
KEY_INDENTED    CONNECT_BY_ISLEAF
--------------- -----------------
server                          0
 bin                            1
 config                         0
  log                           1
 ctx                            0
  admin                         1
  data                          0
   delx                         1
   enlx                         1
   eslx                         1
  mig                           1
 

It is important to recognize that CONNECT_BY_ISLEAF only considers the tree defined by the CONNECT BY condition, not that of the underlying table data.

For example, in table T the rows with a KEY of 'config' and 'ctx' have descendents (children and grandchildren) and are therefore not leaf nodes in that context. In the following query however, those same rows are considered leaf nodes (they have a CONNECT_BY_ISLEAF value of 1) because none of the descendents exist in the tree as defined by the CONNECT BY clause. They are filtered out by the " LEVEL <= 2 " condition.

select
  lpad(' ', level-1 ) || key as key_indented ,
  connect_by_isleaf
from
  t
start with
  key = 'server'
CONNECT BY
  parent_key = prior key and
  LEVEL <= 2  -- filters out descendents of "config" and "ctx"
;
 
KEY_INDENTED    CONNECT_BY_ISLEAF
--------------- -----------------
server                          0
 bin                            1
 config                         1
 ctx                            1
 

As we saw happen with the LEVEL column in the preceding tutorial, the order of evaluation of the CONNECT BY and WHERE clauses can also affect the behaviour of the CONNECT_BY_ISLEAF pseudo column. The following example illustrates this. In it, " LEVEL <= 2 " is placed in the WHERE clause, not the CONNECT BY clause as above, causing CONNECT_BY_ISLEAF to be 0 for "config" and "ctx" even though those rows look like leaf nodes in the end result.

select
  lpad(' ', level-1 ) || key as key_indented ,
  connect_by_isleaf
from
  t
WHERE          -- filter out descendents of "config" and "ctx",
  LEVEL <= 2   -- this time using the WHERE clause
start with
  key = 'server'
connect by
  parent_key = prior key
;
 
KEY_INDENTED    CONNECT_BY_ISLEAF
--------------- -----------------
server                          0
 bin                            1
 config                         0
 ctx                            0
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12570.html]SQL Snippets: Hierarchical Data - CONNECT_BY_ISLEAF[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12570.html">SQL Snippets: Hierarchical Data - CONNECT_BY_ISLEAF</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Hierarchical Data - CONNECT_BY_ISLEAF
  • URL (href): http://www.sqlsnippets.com/en/topic-12570.html