🌳 Zapytania Hierarchiczne

CONNECT BY PRIOR, START WITH, LEVEL, SYS_CONNECT_BY_PATH

⚡ Kluczowe fakty do zapamiętania

→PRIOR stoi przed kolumną RODZICA. W CONNECT BY PRIOR employee_id = manager_id: szukamy wiersza, którego employee_id (PRIOR = rodzic) jest równy manager_id bieżącego węzła. Czyli: rodzic to ktoś, kto jest managerem bieżącego pracownika → przechodzenie Z GÓRY NA DÓŁ.
→Odwrócenie kierunku: CONNECT BY PRIOR manager_id = employee_id — PRIOR jest teraz przy manager_id, czyli szukamy wiersza, którego manager_id równa się employee_id bieżącego → przechodzenie Z DOŁU DO GÓRY (od pracownika do jego szefów).
→LEVEL = głębokość węzła. Korzeń (START WITH) = LEVEL 1, jego dzieci = LEVEL 2, wnuki = LEVEL 3. Pseudokolumna — nie wymaga GROUP BY. Używaj LPAD(' ', 4*(LEVEL-1)) do tworzenia wcięć.
→WHERE vs AND w CONNECT BY — egzaminacyjna pułapka! WHERE last_name != 'X' usuwa TYLKO węzeł X z wyników, ale Oracle przechodzi przez niego do jego dzieci — dzieci X są WIDOCZNE. CONNECT BY ... AND last_name != 'X' odcina X i CAŁE jego poddrzewo.
→SYS_CONNECT_BY_PATH(kol, separator) buduje ścieżkę od korzenia do bieżącego węzła: /King/Kochhar/Greenberg. Zaczyna od separatora. Nie można użyć spacji jako separatora.
→CONNECT_BY_ROOT kolumna = wartość z korzenia drzewa dla każdego wiersza (np. każdy pracownik wie kto jest prezesem). Różni się od SYS_CONNECT_BY_PATH tym, że daje tylko wartość korzenia, nie całą ścieżkę.
→CONNECT_BY_ISLEAF = 1 gdy węzeł nie ma dzieci (jest liściem drzewa). CONNECT_BY_ISCYCLE = 1 gdy wykryto cykl — wymaga słowa kluczowego NOCYCLE w CONNECT BY, inaczej Oracle rzuci ORA-01436.
→ORDER SIBLINGS BY sortuje dzieci tego samego rodzica bez niszczenia struktury drzewa. Zwykły ORDER BY niszczy hierarchię — ZAWSZE używaj ORDER SIBLINGS BY jeśli chcesz sortowanie zachowujące drzewo.

Idea zapytań hierarchicznych

Oracle pozwala przechodzić drzewa relacyjne (np. manager → pracownicy) używając klauzul START WITH i CONNECT BY PRIOR. Dane hierarchiczne to tabela z kluczem i odwołaniem do rodzica w tej samej tabeli.

Podstawowa składnia

sql

SELECT [LEVEL,] kolumny
FROM tabela
START WITH warunek_korzenia
CONNECT BY PRIOR klucz_dziecka = klucz_rodzica
[WHERE dodatkowy_filtr]
[ORDER SIBLINGS BY kolumna];

Element	Opis
`START WITH`	Warunek określający korzeń/korzenie drzewa
`CONNECT BY PRIOR`	Definiuje kierunek przechodzenia (góra↓dół lub dół↑góra)
`LEVEL`	Pseudokolumna — głębokość węzła (1 = korzeń)
`ORDER SIBLINGS BY`	Sortowanie węzłów na tym samym poziomie (nie niszczy hierarchii)
`NOCYCLE`	Ignoruje cykliczne odwołania (bez tego ORA-01436)

Kierunek przechodzenia — PRIOR

sql

-- Z góry na dół (od managera do podwładnych):
CONNECT BY PRIOR employee_id = manager_id
-- "węzeł nadrzędny (PRIOR employee_id) jest managerem bieżącego węzła"

-- Z dołu do góry (od pracownika do managera):
CONNECT BY PRIOR manager_id = employee_id
-- "węzeł nadrzędny (PRIOR manager_id) jest podwładnym bieżącego węzła"

Przykład — drzewo organizacyjne

sql

-- Całe drzewo od prezesa (bez managera)
SELECT LEVEL,
       LPAD(' ', 4*(LEVEL-1)) || first_name || ' ' || last_name AS pracownik,
       employee_id,
       manager_id
FROM hr.employees
START WITH manager_id IS NULL          -- korzeń = brak przełożonego
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

-- Podwładni konkretnego managera (id=100) do 2 poziomów
SELECT LEVEL, first_name, last_name
FROM hr.employees
START WITH manager_id = 100
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2;

SYS_CONNECT_BY_PATH — ścieżka od korzenia

sql

-- Wyświetl pełną ścieżkę np. "King/Kochhar/Greenberg"
SELECT SYS_CONNECT_BY_PATH(last_name, '/') AS sciezka,
       last_name, LEVEL
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- Wynik: /King, /King/Kochhar, /King/Kochhar/Greenberg, ...

CONNECT_BY_ROOT — wartość z korzenia

sql

-- Pokaż każdemu pracownikowi kto jest jego "najwyższym szefem"
SELECT first_name, last_name,
       CONNECT_BY_ROOT last_name AS top_manager
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

CONNECT_BY_ISLEAF — identyfikacja liści

sql

-- 1 = liść drzewa (brak podwładnych), 0 = węzeł wewnętrzny
SELECT first_name, last_name,
       CONNECT_BY_ISLEAF AS czy_lisc
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Tylko liście (pracownicy bez podwładnych):
SELECT first_name, last_name
FROM hr.employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

🚨

WHERE vs AND w CONNECT BY — kluczowa różnica!

WHERE filtruje po przejściu całego drzewa — usunie węzeł ale przejdzie przez niego do dzieci.
AND w CONNECT BY przycina gałąź — węzeł i wszystkie jego dzieci są odcinane.

WHERE last_name != 'King' — usuwa Kinga z wyników, ale pokazuje jego podwładnych
CONNECT BY PRIOR e_id = m_id AND last_name != 'King' — usuwa Kinga i CAŁE drzewo pod nim

Przykład z AND vs WHERE

sql

-- WHERE: pomiń Kinga ale pokaż jego podwładnych
SELECT LEVEL, last_name
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
WHERE last_name != 'King';   -- King pominięty, jego dzieci widoczne

-- AND: odetnij całą gałąź Kinga (on i podwładni znikają)
SELECT LEVEL, last_name
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
    AND last_name != 'King';  -- King i jego poddrzewa znikają

⚖️ Porównanie wyników— WHERE last_name != 'Kochhar' vs AND last_name != 'Kochhar'

WHERE last_name != 'Kochhar' — usuwa węzeł, dzieci zostają

LVL	PRACOWNIK	MGR_ID
1	King	NULL
2	De Haan	100
3	Hunold	102
2	Greenberg	101
2	Faviet	101

AND last_name != 'Kochhar' — odcina całe poddrzewo

LVL	PRACOWNIK	MGR_ID
1	King	NULL
2	De Haan	100
3	Hunold	102

💡 WHERE: Kochhar(101) znika, ale jego podwładni (Greenberg, Faviet) zostają — ich gałąź jest nadal przechodzana. AND w CONNECT BY: Oracle w ogóle nie przechodzi przez Kochhara — cała jego gałąź (Greenberg, Faviet, itd.) jest odcięta.

NOCYCLE — obsługa cykli

sql

-- Gdy dane mają cykliczne odwołania (A→B→C→A)
SELECT employee_id, manager_id, CONNECT_BY_ISCYCLE AS cykl
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;
-- CONNECT_BY_ISCYCLE = 1 gdy wykryto cykl

WHERE level = N — filtrowanie po poziomie

Można filtrować wyniki wg głębokości w drzewie. Uwaga: WHERE level redukuje wyniki PO przejściu drzewa (nie przycina gałęzi).

sql

-- Tylko bezpośredni podwładni pracownika Li (poziom 2 od niego):
SELECT first_name, last_name, employee_id, manager_id, LEVEL
FROM hr.employees
WHERE LEVEL = 2
START WITH last_name = 'Li'
CONNECT BY PRIOR employee_id = manager_id;

-- Tylko bezpośredni przełożony pracownika Baida (poziom 2 w górę):
SELECT LPAD(' ', 4*(LEVEL-1)) || first_name || ' ' || last_name AS tree, LEVEL
FROM hr.employees
WHERE LEVEL = 2
CONNECT BY PRIOR manager_id = employee_id
START WITH last_name = 'Baida';

-- Pracownicy na trzecim poziomie hierarchii (od employee_id = 100):
SELECT employee_id, first_name, last_name, LEVEL
FROM hr.employees
WHERE LEVEL = 3
CONNECT BY PRIOR employee_id = manager_id
START WITH employee_id = 100;

LISTAGG z zapytaniami hierarchicznymi

sql

-- Lista pracowników na każdym poziomie (rozdzielone średnikiem):
SELECT LEVEL,
       LISTAGG(first_name || ' ' || last_name, '; ')
           WITHIN GROUP (ORDER BY last_name) AS pracownicy
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

-- Ścieżka jako lista i count na poziomie:
SELECT LEVEL,
       COUNT(*) AS ile_na_poziomie,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS nazwiska
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

Agregaty na hierarchii

sql

-- Liczba poziomów w drzewie:
SELECT COUNT(DISTINCT LEVEL) AS liczba_poziomow
-- lub: MAX(LEVEL) AS najglebszy_poziom
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

-- Średnie wynagrodzenie na każdym poziomie:
SELECT LEVEL AS poziom,
       ROUND(AVG(salary), 2) AS sr_wynagrodzenie
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

PRIOR w SELECT — wyświetlanie danych rodzica

sql

-- Pracownik → bezpośredni przełożony w jednej kolumnie:
SELECT first_name || ' ' || last_name
       || ' -> '
       || PRIOR last_name || ' ' || PRIOR first_name AS lancuch
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

-- PRIOR w SELECT: odwołuje się do wartości wiersza nadrzędnego (rodzica)
-- CONNECT_BY_ROOT w SELECT: odwołuje się do korzenia (można też w SELECT)

Pytania egzaminacyjne

🔤

Pytanie ABCD

CONNECT BY PRIOR employee_id = manager_id powoduje przechodzenie drzewa...

🔤

Pytanie ABCD

Jaka jest różnica między WHERE last_name != 'King' a AND last_name != 'King' w CONNECT BY?

🔤

Pytanie ABCD

CONNECT_BY_ISLEAF = 1 oznacza, że węzeł...

🔤

Pytanie ABCD

SYS_CONNECT_BY_PATH(last_name, '/') zwraca dla pracownika na poziomie 3...

🔍 Czy to się wykona?

SELECT LEVEL, employee_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id;

🔍 Czy to się wykona?

SELECT LEVEL, last_name
FROM hr.employees
WHERE LEVEL <= 2
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

🔍 Czy to się wykona?

SELECT employee_id, CONNECT_BY_ISCYCLE AS cykl
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

🔎 Znajdź błąd — wskaż niepoprawną linię

1SELECT LEVEL, last_name

2FROM hr.employees

3START WITH manager_id IS NULL

4CONNECT BY employee_id = manager_id;

🔎 Znajdź błąd — wskaż niepoprawną linię

1SELECT LEVEL, last_name, CONNECT_BY_ISCYCLE AS cykl

2FROM hr.employees

3START WITH manager_id IS NULL

4CONNECT BY PRIOR employee_id = manager_id;

🔎 Znajdź błąd — wskaż niepoprawną linię

1SELECT LEVEL, last_name

2FROM hr.employees

3WHERE LEVEL = 2

4CONNECT BY PRIOR employee_id = manager_id

5START WITH employee_id = 100

6ORDER SIBLINGS BY last_name;

Ćwiczenia

💪

Ćwiczenie 1 — Drzewo z wcięciami

Wyświetl całe drzewo organizacyjne firmy (od prezesa). Użyj LPAD i LEVEL do tworzenia wcięć. Pokaż LEVEL, imię, nazwisko i id managera. Sortuj rodzeństwo po nazwisku.

💪

Ćwiczenie 2 — Ścieżka od korzenia i top manager

Dla każdego pracownika wyświetl: nazwisko, pełną ścieżkę od prezesa (SYS_CONNECT_BY_PATH z separatorem '/'), nazwisko najwyższego przełożonego (CONNECT_BY_ROOT) oraz czy jest liściem.

💪

Ćwiczenie 3 — Podwładni managera z ograniczeniem głębokości

Wyświetl wszystkich podwładnych pracownika o employee_id = 101 (bezpośrednich i pośrednich) maksymalnie do 3 poziomów głębokości. Pokaż LEVEL i nazwisko.

💪

Ćwiczenie 4 — Przełożeni pracownika Gruenberg

Wyświetl pracownika o nazwisku Gruenberg i WSZYSTKICH jego przełożonych (w górę hierarchii). Pokaż employee_id, imię, nazwisko i LEVEL.

💪

Ćwiczenie 5 — Tylko bezpośredni podwładni (WHERE LEVEL = 2)

Wyświetl pracownika Li i tylko jego bezpośrednich podwładnych (nie pośrednich). Pokaż imię, nazwisko, employee_id i LEVEL.

💪

Ćwiczenie 6 — Średnia pensja na każdym poziomie + liczba poziomów

(A) Wyświetl średnią pensję na każdym poziomie hierarchii (od employee_id=100). (B) Podaj liczbę poziomów w całym drzewie.