CTE (Common Table Expression), zdefiniowana za pomocą WITH, to nazwany tymczasowy zestaw wyników, na który można odwoływać się w ramach zapytania. CTE-y zwiększają czytelność złożonych zapytań, rozkładając je na nazwane, logiczne kroki — oraz umożliwiają rekurencyjne zapytania (dla hierarchii).
active_users (
id, name users active
)
active_users
name ;
Klauzula WITH ... AS (...) definiuje CTE — nazwane zapytanie, do którego odwołujesz się później. Jest to podobne do podzapytania, ale nazwane i czytelne, szczególnie w przypadku złożonych zapytań.
WITH
monthly_sales AS (
SELECT month, SUM(amount) AS total FROM orders GROUP BY month
),
avg_sales AS (
SELECT AVG(total) AS avg_total FROM monthly_sales -- a CTE can reference another
)
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total; -- months above the average
Wiele CTE-ów rozkłada złożone zapytanie na czytelne, sekwencyjne kroki — każdy buduje się na poprzednim. To jest znacznie jaśniejsze niż głęboko zagnieżdżone podzapytania.
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- base case (the top)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- recursive step
)
SELECT * FROM subordinates; -- the whole tree under employee 1
Rekurencyjne CTE (WITH RECURSIVE) przechodzą przez dane hierarchiczne/drzewiaste (schematy organizacyjne, drzewa kategorii, struktury materiałów, ścieżki grafów) — coś, co w inny sposób jest bardzo trudne w SQL.
CTE są ważną funkcją SQL dla czytelności i rekursji, a ich zrozumienie jest cenne dla pisania utrzymywalnych złożonych zapytań oraz obsługi danych hierarchicznych.
Pierwotną korzyścią na co dzień jest czytelność: złożone zapytania z wieloma poziomami podzapytań stają się trudne do odczytania i utrzymania, podczas gdy CTE-y pozwalają rozbić zapytanie na nazwane, sekwencyjne logiczne kroki (czytane od góry do dołu jak seria operacji), dramatycznie poprawiając jasność — z tego powodu CTE-y są często preferowane zamiast głęboko zagnieżdżonych podzapytań w złożonych zapytaniach, czyniąc je ważnym narzędziem do pisania utrzymywalnego SQL.
Równie ważna jest możliwość rekurencyjnego CTE (WITH RECURSIVE), które umożliwia wyszukiwanie danych hierarchicznych i drzewiasto-strukturalnych (schematy organizacyjne, drzewa kategorii, struktury materiałów, przechodzenie grafów) — klasa zapytań, która w inny sposób jest bardzo trudna lub niemożliwa w standardowym SQL, czyniąc rekurencyjne CTE-y standardowym rozwiązaniem dla przechodzenia danych hierarchicznych.
Zrozumienie obu zastosowań — CTE do rozkładania złożonych zapytań na czytelne kroki oraz rekurencyjnych CTE do przechodzenia hierarchii — obejmuje praktyczną wartość.
Ponieważ złożone zapytania są częste w rzeczywistych aplikacjach (a czytelność/utrzymywalność mają znaczenie), a dane hierarchiczne (które rekurencyjne CTE-y obsługują wyjątkowo dobrze) pojawiają się często (struktury organizacyjne, zagnieżdżone kategorie, dane wątkowe), zrozumienie CTE-ów jest cenną wiedzą na poziomie seniorskim, która poprawia utrzymywalność zapytań i umożliwia wyszukiwanie hierarchiczne — często odpowiednie narzędzie do pisania czystego, zaawansowanego SQL oraz powszechny temat, który demonstruje zaawansowaną zdolność SQL, szczególnie formę rekurencyjną dla danych drzewiasto-strukturalnych.
Biblioteka pytań rekrutacyjnych IT ze szczegółowymi odpowiedziami — od Juniora do Seniora.
Wesprzyj