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