Poznanie CTE: WITH

W programowaniu możesz wydzielić kawałek kodu i nadać mu nazwę — stworzyć funkcję. Tak samo jest z CTE. Możesz wydzielić SELECT-subquery poza główne zapytanie, nadać mu nazwę i potem użyć go w głównym SQL-query.

CTE (Common Table Expressions, czyli wspólne wyrażenia tabelaryczne) — to jak świeży oddech dla programisty zmęczonego zagnieżdżonymi zapytaniami. Sprawiają, że SQL-code jest nie tylko czytelny, ale i naprawdę elegancki. Jeśli wcześniej siedziałeś nad rozbudowanymi konstrukcjami z subquery i miałeś oczopląs — czas poznać "magię" CTE.

Wyobraź sobie, że budujesz dom. Zwykle chcesz jak najszybciej wstawić okna, przykręcić drzwi (czyli od razu wrzucić zagnieżdżone zapytania), nawet jeśli ściany jeszcze nie stoją. A z CTE jest inaczej: najpierw robisz schludny szkic — tworzysz tymczasową tabelę, jakby plan domu. A potem, krok po kroku, budujesz kolejne piętra zapytania. Stylowo, solidnie, technicznie.

W gruncie rzeczy CTE — to wirtualne tabele, które tworzysz na bieżąco za pomocą SELECT-query. Coś jak subquery, tylko lepsze. Jeśli w programowaniu możesz wydzielić kawałek logiki do osobnej funkcji z czytelną nazwą, to w SQL tę rolę przejmuje CTE. Piszesz SELECT, nadajesz mu nazwę — i używasz dalej jako część dużego, złożonego zapytania. Fajnie? No jasne.

Przykład SQL-query z subquery:

-- główne zapytanie
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subquery, które dostało alias filtered_students

Wydzieliliśmy subquery osobno:

-- CTE/subquery, które dostało alias filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- główne zapytanie
SELECT *
FROM filtered_students;

Niesamowite, ale subquery pojawiły się 20 lat wcześniej niż CTE! W standardzie SQL-89 już były subquery, a CTE pojawiły się dopiero w SQL-2009.

Składnia WITH

CTE zaczyna się od słowa kluczowego WITH i wygląda mniej więcej tak:

WITH cte_name AS (
    SELECT ... -- twoje zapytanie tutaj
)

SELECT ...
FROM cte_name;

Tutaj:

• cte_name — to nazwa twojego CTE. Możesz wybrać dowolną sensowną nazwę, np. high_scores, filtered_data albo nawet best_students.
• W nawiasach () piszesz zapytanie, które przygotowuje dane do dalszego użycia.
• Po zdefiniowaniu CTE możesz się do niego odwoływać jak do zwykłej tabeli w głównym zapytaniu.

Przykład 1: Prosty CTE

Zobaczmy, jak działa CTE na żywym przykładzie. Załóżmy, że mamy tabelę students — listę studentów z ich ocenami:

Naszym celem jest wybrać wszystkich studentów z oceną powyżej 85 i wyświetlić ich dane.

Wersja bez CTE:

Można to zrobić za pomocą subquery:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

A tak samo, ale z CTE — dużo przyjemniej dla oka:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Przyznaj, wygląda czyściej i czytelniej. Wyraźnie oddzieliliśmy przygotowanie danych (WITH) od głównej części zapytania (SELECT). To jakby najpierw ogarnąć biurko, a potem dopiero zacząć pracę — od razu lepiej się oddycha.

Przykład 2: Kilka CTE

Możesz zdefiniować kilka CTE w jednym zapytaniu. To szczególnie przydatne, jeśli dane trzeba przygotować etapami.

Dane: tabela grades, gdzie są oceny studentów z kursów:

Zadanie: dla każdego studenta znaleźć średnią ocenę, a potem wybrać tych, którzy mają powyżej 85.

Rozwiązanie z kilkoma CTE:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- odwołujemy się do pierwszego CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- odwołujemy się do drugiego CTE - high_achievers

Tutaj:

1. student_averages przygotowuje wstępne dane — średnie oceny studentów.
2. high_achievers używa tych danych, żeby wybrać tylko tych, którzy mają oceny powyżej 85.

Różnica między CTE a subquery

Spoiler: CTE nie zastępują subquery, ale w niektórych przypadkach są dużo wygodniejsze.

Subquery — to zapytanie w zapytaniu. Są przydatne, gdy chcesz szybko dostać wynik, ale jeśli jest ich dużo, kod zamienia się w chaos.

Przykład:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Subquery mogą być w SELECT, w FROM, w WHERE i w HAVING. Poza tym mogą odwoływać się do kolumn zewnętrznego zapytania. Z tym ostatnim CTE mają trudniej.

Z kolei CTE pozwala zrobić kod dużo bardziej czytelnym, więc łatwiej go utrzymać i jest w nim mniej błędów. Zamiast zagnieżdżać jedno zapytanie w drugim, CTE pozwala po prostu "nazwać" wynik subquery i użyć go dalej.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTE są szczególnie przydatne, jeśli musisz użyć przygotowanych danych kilka razy w jednym zapytaniu.

Kiedy używać CTE?

• Kiedy musisz podzielić złożone zapytanie na kilka logicznych etapów.
• Jeśli zapytanie ma być czytelne i łatwe w utrzymaniu. Nikt nie chce grzebać się w zagnieżdżonych strukturach kodu przypominających spaghetti.
• Do tymczasowego przygotowania danych, które wykorzystuje tylko bieżące zapytanie.

Ostatni przykład: analiza kursów

Połączmy wszystko, czego się nauczyliśmy:

1. Znajdźmy studentów z wysoką średnią oceną.
2. Wyświetlmy ich imiona i kursy, na które są zapisani.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Zwróć uwagę, jak wszystko jest poukładane:

1. Najpierw przygotowaliśmy średnie oceny.
2. Potem wybraliśmy tylko najlepszych studentów.
3. Następnie powiązaliśmy ich z kursami.

Teraz oficjalnie jesteś gotowy, żeby zacząć używać CTE do tworzenia ładnych, czytelnych i mocnych SQL-query.

Do dzieła — do własnych projektów!