Jak działa CTE (WITH) w języku SQL? Przykłady zastosowania

„`html

Jeśli kiedykolwiek zdarzyło Ci się pisać skomplikowane zapytania SQL, to na pewno spotkałeś się z sytuacją, w której konieczne było wielokrotne używanie tej samej podzapytania. Właśnie w takich momentach warto sięgnąć po CTE (Common Table Expression), znany także jako składnia WITH. To potężne narzędzie pozwala na zwiększenie czytelności kodu SQL oraz jego optymalizację. W tym artykule wyjaśnię, jak działa CTE w SQL oraz pokażę przykłady jego praktycznego zastosowania.

Co to jest CTE i jak działa?

CTE, czyli Common Table Expression, to tymczasowy wynik zapytania SQL, który można traktować jak tabelę w kolejnych elementach zapytania. Jest to struktura bardzo podobna do podzapytania, z tą różnicą, że jest bardziej czytelna oraz umożliwia wielokrotne użycie w ramach tego samego zapytania.

Podstawowa składnia CTE wygląda następująco:

WITH nazwa_cte AS (
    SELECT kolumny
    FROM tabela
    WHERE warunek
)
SELECT * FROM nazwa_cte;

W pierwszej części tworzymy tymczasowy zestaw danych o określonej nazwie (w tym przypadku nazwa_cte), a następnie w drugiej części odwołujemy się do tego zestawu w głównym zapytaniu SELECT.

Przykład użycia CTE – filtrowanie danych

Załóżmy, że mamy tabelę pracownicy i chcemy znaleźć wszystkich pracowników z działu IT, którzy zarabiają więcej niż 5000:

WITH pracownicy_it AS (
    SELECT imie, nazwisko, pensja
    FROM pracownicy
    WHERE dzial = 'IT'
)
SELECT * 
FROM pracownicy_it 
WHERE pensja > 5000;

CTE pozwala nam najpierw wybrać wszystkich pracowników z działu IT, a następnie na ich podstawie przeprowadzić dodatkową filtrację – w tym przypadku sprawdzamy, kto zarabia więcej niż 5000.

Rekurencyjne CTE – kiedy się przydaje?

Jednym z ciekawszych zastosowań CTE jest możliwość tworzenia zapytań rekurencyjnych. Jest to szczególnie przydatne przy analizie struktur hierarchicznych, np. drzew kategorii lub relacji przełożony-pracownik.

Oto przykład rekurencyjnego CTE, które zwraca strukturę organizacyjną firmy:

WITH RECURSIVE hierarchia AS (
    SELECT id, imie, nazwisko, id_przelozonego
    FROM pracownicy
    WHERE id_przelozonego IS NULL
    
    UNION ALL

    SELECT p.id, p.imie, p.nazwisko, p.id_przelozonego
    FROM pracownicy p
    INNER JOIN hierarchia h ON p.id_przelozonego = h.id
)
SELECT * FROM hierarchia;

W tym przypadku najpierw wybieramy wszystkich pracowników bez przełożonego (najwyższy poziom struktury), a następnie rekurencyjnie dodajemy ich podwładnych.

Dlaczego warto używać CTE?

CTE ma wiele zalet, które czynią go niezwykle przydatnym narzędziem w pracy z bazami danych:

• Poprawia czytelność kodu – zamiast skomplikowanych podzapytań można użyć bardziej przejrzystej struktury WITH.
• Ułatwia debugowanie – można łatwo analizować poszczególne części zapytania.
• Pozwala na wielokrotne wykorzystanie tych samych danych wewnątrz jednego zapytania.
• Obsługuje zapytania rekurencyjne, co jest niezwykle przydatne przy analizie hierarchicznych struktur danych.

CTE a podzapytania i tabele tymczasowe – porównanie

Na koniec warto porównać CTE z dwoma innymi popularnymi metodami organizacji kodu SQL – podzapytaniami (subqueries) oraz tabelami tymczasowymi (temporary tables):

Metoda	Zalety	Wady
CTE (WITH)	Poprawia czytelność, obsługuje rekurencję, łatwa do debugowania	Nie zawsze jest najbardziej wydajna
Podzapytanie	Bezpośrednia implementacja w zapytaniu	Może być trudne do czytania i debugowania
Tabela tymczasowa	Może poprawić wydajność w skomplikowanych operacjach	Wymaga jawnego tworzenia i usuwania tabel

W zależności od kontekstu, każde z tych podejść ma swoje zastosowania. Jeśli jednak chodzi o czytelność i elastyczność, CTE często okazuje się najlepszym wyborem.

Podsumowanie

CTE (konstrukcja WITH) to potężna, choć często niedoceniana funkcjonalność SQL, która pomaga lepiej organizować kod, czyni go bardziej czytelnym i ułatwia pracę z bazami danych. W swojej pracy często korzystam z CTE, zwłaszcza gdy tworzę zapytania wymagające wielokrotnego użycia tych samych danych lub gdy pracuję z hierarchiami.

Jeśli jeszcze nie używasz CTE, warto się nim zainteresować – ma szereg zalet i w wielu przypadkach upraszcza pracę nad SQL.

„` Inny ciekawy artykuł: Jak działa PARTITION BY w języku SQL? Przykłady zastosowania