Rekursywne CTE: co to jest i po co to komu

Dziś zrobimy kolejny krok do przodu i zajmiemy się magią rekurencji. Jeśli już kiedyś programowałeś w języku z obsługą rekurencji (np. Python), to mniej więcej wiesz, o co chodzi. Ale spokojnie, jeśli brzmi to tajemniczo — wszystko rozłożymy na czynniki pierwsze.

Rekursywne CTE to potężne narzędzie do pracy z hierarchicznymi, drzewiastymi strukturami danych, takimi jak struktury organizacyjne firm, drzewo genealogiczne czy katalogi plików.

Mówiąc prosto, to takie wyrażenia, które mogą "wywołać same siebie", żeby stopniowo przejść i ogarnąć wszystkie poziomy danych.

Kluczowe cechy rekursywnych CTE:

1. Używają słowa kluczowego WITH RECURSIVE.
2. Rekursywne CTE składają się z dwóch części:
   • Zapytanie bazowe: określa punkt startowy (czyli "korzeń") rekurencji.
   • Zapytanie rekursywne: ogarnia resztę danych, korzystając z wyniku poprzedniego kroku.

Algorytm działania rekursywnego CTE przypomina wchodzenie po schodach:

• Najpierw stajesz na pierwszym stopniu (to zapytanie bazowe).
• Potem wchodzisz na drugi stopień, korzystając z wyniku pierwszego (zapytanie rekursywne).
• Proces powtarza się, aż skończą się schody (osiągnięcie warunku zakończenia).

Składnia rekursywnego CTE

Od razu rzućmy okiem na szablonowy przykład:

WITH RECURSIVE cte_name AS (
    -- Zapytanie bazowe
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- Zapytanie rekursywne
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

Rola UNION i UNION ALL w rekursywnych CTE

Każdy rekursywny CTE musi używać operatorów UNION lub UNION ALL między częścią bazową a rekursyjną.

Jaki operator wybrać: UNION czy UNION ALL?

Jeśli nie jesteś pewien, co wybrać — prawie zawsze bierz UNION ALL. Dlaczego? Bo działa szybciej: po prostu skleja wyniki, nie sprawdzając, czy są tam duplikaty. To znaczy — mniej obliczeń, mniej zasobów i szybciej masz wynik.

Szczególnie ważne to jest w rekursywnych CTE. Gdy budujesz hierarchie — np. drzewo komentarzy albo strukturę podwładnych w firmie — UNION ALL jest prawie zawsze potrzebny. Jeśli użyjesz zwykłego UNION, baza może przypadkiem uznać, że jakieś kroki już były i „uciąć” część wyniku. A to rozwali całą logikę przechodzenia.

Używaj UNION tylko wtedy, gdy wiesz na 100%, że duplikaty są szkodliwe i trzeba je wywalić. Ale pamiętaj: to zawsze kompromis między czystością a szybkością.

Przykład różnych podejść

-- UNION: duplikaty są wykluczane
SELECT 'A'
UNION
SELECT 'A';     -- Wynik: jeden wiersz 'A'

-- UNION ALL: duplikaty zostają
SELECT 'A'
UNION ALL
SELECT 'A';     -- Wynik: dwa wiersze 'A'

W zapytaniach rekursywnych bezpieczniej zawsze używać UNION ALL, żeby nie zgubić ważnych kroków przy przechodzeniu po strukturze.

Weźmy typowe zadanie: mamy tabelę pracowników z kolumnami employee_id, manager_id i name. Trzeba zbudować hierarchię, zaczynając od dyrektora — osoby bez szefa (czyli manager_id = NULL).

Załóżmy, że mamy tabelę pracowników: employees

Musimy ogarnąć, kto komu podlega i poznać poziom każdego pracownika w strukturze. To wygodne, gdy chcesz np. wyświetlić drzewo pracowników w interfejsie albo przygotować raport o strukturze zespołu.

WITH RECURSIVE employee_hierarchy AS (
    -- Zaczynamy od tych, którzy nie mają szefa
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Dodajemy podwładnych i zwiększamy poziom
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Wynik będzie taki:

To zapytanie fajnie pokazuje, jak można "przejść" po hierarchii pracowników — od dyrektora do najniższych w strukturze. Poziom level przydaje się do formatowania albo wizualizacji drzewa.

Przykład: kategorie produktów

Wyobraź sobie teraz, że pracujesz z tabelą kategorii produktów, gdzie każda kategoria może mieć podkategorie, a te z kolei swoje podkategorie. Jak zbudować drzewo kategorii?

Tabela categories

Rekursywne zapytanie:

WITH RECURSIVE category_tree AS (
    -- Przypadek bazowy: znajdź kategorie główne
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Część rekursywna: znajdź podkategorie aktualnych kategorii
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Wynik:

Teraz widzimy drzewo kategorii z poziomami zagłębienia.

Dlaczego rekursywne CTE są super?

Rekursywne CTE to jeden z najbardziej wyrazistych i potężnych narzędzi SQL. Zamiast skomplikowanej zagnieżdżonej logiki po prostu opisujesz, od czego zacząć (przypadek bazowy) i jak iść dalej (część rekursyjną) — resztę robi PostgreSQL.

Najczęściej takie zapytania używa się do przechodzenia po hierarchiach: pracowników, kategorii produktów, katalogów na dysku, grafów w social mediach. Łatwo je rozszerzać: jeśli do tabeli dojdą nowe dane, zapytanie samo je ogarnie. To wygodne i skalowalne.

Ale są też pułapki. Koniecznie pilnuj warunków zakończenia — bez nich zapytanie może wpaść w nieskończoną pętlę. Nie zapomnij o indeksach: w dużych tabelach rekursywne zapytania bez nich mogą zamulić. A UNION ALL — prawie zawsze najlepszy wybór, szczególnie w zadaniach hierarchicznych, bo inaczej możesz zgubić kroki rekurencji przez usuwanie duplikatów.

Dobrze ustawiony rekursywny CTE pozwala wyrazić skomplikowaną logikę biznesową dosłownie w kilku linijkach — bez procedur, pętli i dodatkowego kodu. To ten przypadek, gdy SQL działa nie tylko poprawnie, ale i elegancko.

Typowe błędy przy pracy z rekursywnymi CTE

• Nieskończona rekurencja: jeśli nie ustawisz poprawnego warunku zakończenia (WHERE), zapytanie może się zapętlić.
• Nadmiarowe dane: złe użycie UNION ALL dodaje duplikaty.
• Wydajność: rekursywne zapytania mogą być ciężkie przy dużej ilości danych. Indeksy na kluczowych kolumnach (np. manager_id) przyspieszą wykonanie.

Kiedy bez rekursywnych zapytań się nie obejdzie

Czasem wydaje się, że rekursywne zapytania to coś z teorii, ale w praktyce pojawiają się często w codziennym kodowaniu. Na przykład:

• żeby zbudować raporty o strukturze firmy albo klasyfikacji produktów;
• żeby przejść po drzewie folderów i zebrać listę wszystkich podkatalogów;
• żeby analizować grafy — powiązania społeczne, trasy, zależności między zadaniami;
• żeby po prostu pokazać skomplikowane powiązania między obiektami w czytelny sposób.

Jeśli musisz przejść po strukturze, gdzie jedno zależy od drugiego — prawie na pewno przyda się WITH RECURSIVE.