Опция START WITH задает условия выбора начальной записи иерархической структуры данных.

Опция CONNECT BY <​условие связи​> задает отношение между узлами иерархической структуры данных. Часть элементов <​условия связи​> должна быть использована в опции PRIOR для ссылки на предыдущий уровень иерархии.

Опция PRIOR устанавливает предыдущий уровень текущей записи иерархии. PRIOR – унарный оператор и обрабатывается как обычные унарные + и - операторы. В неиерархических запросах при одной исходной таблице во всех <​условиях связи​> имена столбцов (не считая внешних ссылок) рассматриваются как ссылки к одной и той же записи исходной таблицы. Напротив, в иерархическом запросе <​условие связи​> должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые оно и связывает между собой.

В <​условиях связи​> <​предыдущий уровень​> и <​последующий уровень​> можно менять местами. <​Условие связи​> должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые связывает между собой. Но так как в иерархическом запросе может участвовать только одна таблица, то различие между обращениями к записям текущего и предыдущего уровня делается следующим образом: если перед аргументом предиката стоит ключевое слово PRIOR, то все обращения к столбцам в этом аргументе предиката относятся к записи предыдущего уровня, если же PRIOR не стоит – то к записи текущего уровня.

Эти конструкции эквивалентны:

 SELECT *
   FROM "Служащие"
  START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";

 SELECT *
   FROM "Служащие"
  START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY "Таб_Номер_Руковод" = PRIOR "Таб_Номер";

<​Предыдущий уровень​> и <​последующий уровень​> должны ссылаться на <​значимые выражения​>, содержащие разные столбцы таблицы.

Типы данных <​предыдущего уровня​> и <​последующего уровня​> должны быть совместимыми.

Условия WHERE, START и CONNECT в иерархических запросах можно задавать в произвольном порядке.

Столбец LEVEL внутри иерархического запроса используется для обозначения уровня иерархии, в остальных запросах – как идентификатор (имя столбца).

create table tst(s int, f int);
insert into tst values (1,2), (2,3);

SELECT level, COUNT(*) as COUNT
  FROM (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f) a
 GROUP BY level;
или
  WITH t1 as (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f)
SELECT level, COUNT(*) as COUNT
  FROM t1 GROUP BY level;

 LEVEL       COUNT
 -----       -----
|          1|          1|
|          2|          1|

Опция SIBLINGS в <​ORDER BY-спецификации​> задаёт порядок сортировки записей внутри уровней иерархического запроса. (Без сортировки вывод записей выполняется в порядке обхода иерархического дерева, т.е. сверху-вниз, слева-направо).

create or replace table test(name char(5), id int, parent int);
insert into test values ('F', 1, NULL);
insert into test values ('C', 2, 1);
insert into test values ('H', 3, 2);
insert into test values ('E', 4, 2);
insert into test values ('B', 5, 1);
insert into test values ('G', 6, 5);
insert into test values ('D', 7, 6);
insert into test values ('A', 8, 5);

а) обычная сортировка

 select name, id, parent, level
   from test start with parent is null
connect by prior id = parent order by name;

 NAME  ID          PARENT      LEVEL
 ----  --          ------      -----
|A    |          8|          5|          3|
|B    |          5|          1|          2|
|C    |          2|          1|          2|
|D    |          7|          6|          4|
|E    |          4|          2|          3|
|F    |          1|       NULL|          1|
|G    |          6|          5|          3|
|H    |          3|          2|          3|

б) иерархическая сортировка

 select name, id, parent, level
   from test start with parent is null
connect by prior id = parent order siblings by name;

 NAME  ID          PARENT      LEVEL
 ----  --          ------      -----
|F    |          1|       NULL|          1|
|B    |          5|          1|          2|
|A    |          8|          5|          3|
|G    |          6|          5|          3|
|D    |          7|          6|          4|
|C    |          2|          1|          2|
|E    |          4|          2|          3|
|H    |          3|          2|          3|

Запрещается использовать одновременно <​WHERE-спецификацию​> и <​ORDER BY-спецификацию​> с опцией SIBLINGS. Т.е. запрос вида

 select * from test start with parent is null
connect by prior id = parent where id <​​> 1 order siblings by name;

является синтаксически неправильным. Будет выдан код завершения 2279 «Использование конструкции ORDER SIBLINGS недопустимо в данном запросе».

Псевдостолбцы ROWID и ROWTIME в иерархических запросах запрещены. Например, нижеследующие запросы синтаксически некорректны:

 select * from TEST start with PARENT is null
connect by prior ID = PARENT ORDER by ROWID;

 select * from TEST start with PARENT is null
connect by prior ID = PARENT ORDER siblings by ROWID;

Опция GROUP BY в иерархических запросах разрешена, например, нижеследующий запрос:

 select COUNT(*) COUNT, LEVEL from test start with PARENT is null
connect by prior ID = PARENT GROUP BY LEVEL;

 COUNT LEVEL
 ----- -----
|    1|    1|
|    2|    2|
|    4|    3|
|    1|    4|

Результирующая таблица по умолчанию имеет тот же список столбцов, что и исходная, с добавлением псевдостолбца с именем «LEVEL» типа INTEGER, который указывает номер уровня записи в иерархии.

Наличие псевдостолбца с именем «LEVEL» в выборке вида SELECT * FROM … зависит от ключа /COMPATIBILITY=ORACLE в команде запуска ядра СУБД:

Псевдостолбец LEVEL можно указывать в <​основном запросе выборки​> и <​WHERE-спецификации​> и нельзя в конструкциях START WITH и CONNECT BY.

Сортировка записей в ответе (если не задана <​GROUP BY-спецификация​>) выполняется в таком порядке, чтобы все записи, подчиненные в иерархии некоторой записи, следовали за ней.

Условие, содержащееся в конструкции START, определяет записи верхнего уровня в иерархии.

Условие, содержащееся в конструкции CONNECT, определяет связи между записями предыдущего и последующего уровней.

<​Иерархический запрос​> выполняется столько раз, сколько <​начальных уровней​> найдено в таблице, задаваемой <​FROM-спецификацией​>.

Максимальное количество уровней для сортируемых иерархических запросов равно 4096.

Если в иерархии записей обнаружено нарушение структуры дерева, фиксируется исключительная ситуация.

Для запросов с CONNECT BY можно подавать команды позиционного обновления и удаления (WHERE CURRENT OF).