CONNECT BY est la clause Oracle qui permet d’interroger des données hiérarchiques — organigrammes, nomenclatures, catégories imbriquées — en suivant les relations parent/enfant. Combinée à START WITH et à la pseudo-colonne LEVEL, elle parcourt un arbre en une seule requête. Voici comment l’utiliser.
Le cas type : un organigramme
Table employes(id, nom, manager_id) où manager_id pointe vers le supérieur. On veut afficher la hiérarchie.
SELECT LEVEL, nom, manager_id
FROM employes
START WITH manager_id IS NULL -- la racine (le PDG)
CONNECT BY PRIOR id = manager_id; -- l'enfant pointe vers son parentSTART WITH: le(s) point(s) de départ de l’arbre.CONNECT BY PRIOR id = manager_id: la règle de liaison parent → enfant.LEVEL: profondeur dans l’arbre (1 = racine).
Comprendre PRIOR (le sens du parcours)
PRIOR désigne la ligne « parent » déjà trouvée. PRIOR id = manager_id signifie : « l’enfant est celui dont le manager_id = l’id du parent ». Inverser PRIOR remonte l’arbre au lieu de le descendre.
Indenter pour visualiser l’arbre
SELECT LPAD(' ', (LEVEL-1)*3) || nom AS arbre
FROM employes
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;LPAD ajoute une indentation proportionnelle au niveau.
Les pseudo-colonnes et fonctions hiérarchiques
LEVEL: profondeur.CONNECT_BY_ROOT colonne: la valeur à la racine de la branche.SYS_CONNECT_BY_PATH(nom, '/'): le chemin complet (« /PDG/Directeur/Chef »).CONNECT_BY_ISLEAF: 1 si la ligne est une feuille (sans enfant).
SELECT nom, SYS_CONNECT_BY_PATH(nom, ' > ') AS chemin
FROM employes
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;Trier les frères : ORDER SIBLINGS BY
... CONNECT BY PRIOR id = manager_id
ORDER SIBLINGS BY nom; -- trie les enfants d'un même parent sans casser l'arbreÉviter les boucles infinies
Si les données contiennent un cycle (A → B → A), Oracle lève ORA-01436. Utilisez CONNECT BY NOCYCLE et la pseudo-colonne CONNECT_BY_ISCYCLE pour les détecter.
CONNECT BY vs CTE récursive
Depuis Oracle 11g R2, le standard SQL propose aussi les CTE récursives (WITH … UNION ALL) — voir notre tutoriel clause WITH. CONNECT BY reste plus concis et propre à Oracle ; la CTE récursive est portable vers d’autres SGBD.
FAQ
À quoi sert START WITH ?
À définir la racine de la hiérarchie (le point de départ). Sans lui, Oracle part de toutes les lignes.
Comment obtenir le chemin complet d’un élément ?
Avec SYS_CONNECT_BY_PATH(colonne, séparateur), qui construit la suite des ancêtres.
CONNECT BY ou WITH récursif ?
CONNECT BY = plus court, spécifique Oracle. CTE récursive (WITH) = standard SQL, portable. Les deux donnent le même résultat.