Klauzula WITH

Wyrażenie WITH zostało wprowadzone w standardowym języku SQL do uproszczenia długich i skomplikowanych zapytań, szczególnie tych z JOIN’ami i subkwerend. Często nazywany jako CTE lub refaktoring subkwerend wyrażenie WITH definiuje tymczasowy zbiór danych, którego wynik może być wykorzystywany w innych zapytaniach.

Najlepsza droga do nauki pracy z wyrażeniem WITH w SQL jest poprzez praktykę. Klauzula WITH jest uważana za tymczasową, ponieważ jej wynik nie jest nigdzie przechowywany wewnątrz bazy danych, lecz wykorzystywany jako tymczasowy widok, który istnieje tylko podczas trwania zapytania, to znaczy, że jest dostępny tylko kiedy baza danych jest odpytywana poprzez wyrażenia SELECT, INSERT, UPDATE, DELETE lub MERGE. Warunek WITH działa tylko z kwerendą, do której należy usprawniając strukturę zapytania bez zwiększania ilości dodatkowych aliasów. Wyrażenie WITH zostało wprowadzone w 1999 i jest dostępne dziś w większości relacyjnych baz danych. Z wyrażeniem tym możemy:

• Wielokrotnie odwoływać się do tabel tymczasowych wewnątrz pojedynczego zapytania;
• Dokonywać wielopoziomowych agregacji, np. wyszukiwać średnie z ekstremów;
• Dokonywać identycznych kalkulacji w kontekście większego zapytania;
• Wykorzystywać jako alternatywę tworzenia widoku w bazie danych.

Więc kiedy tak naprawdę potrzebujemy wyrażenia WITH? Istnieje kilka przypadków kiedy jego użycie jest wskazane. Większość z nich dotyczy usprawnienia tworzenia zapytania i uproszczenia zarządzania zmianą. Za najważniejsze zalety korzystania z WITH możemy uznać:

• Zwiększenie przejrzystości kodu – pozwala na organizację kodu w taki sposób, aby minimalnym wysiłkiem zrozumieć jego zależności. Klauzula WITH pozwala na podział większego zapytania na kilka mniejszych i łatwiejsze zrozumienie poszczególnych kalkulacji.
• Uproszczenie zarządzania zmianą – warunek WITH wpływa zarówno na przejrzystość jak i zarządzanie zmianą. Jako że nasze zapytania i bazy danych rozrastają się w czasie prowadzi to do potrzeby debugowania i usprawniania bazy danych. Kod, który łatwiej czytać jest również łatwiejszy w utrzymaniu.
• Alternatywa do widoków – WITH potrafi zastąpić widoki. Może to być przydatne kiedy kiedy nie masz ustawień systemowych, aby tworzyć widok, lub jeśli nie chcesz tworzyć widoku, który będzie używany tylko do pojedynczego zapytania.
• Procesowanie hierarchicznych struktur – to jest jedno z bardziej zaawansowanych zastosowań i pozwala na odwoływanie się do kompleksowych zapytań. Pozwala to na tworzenie hierarchicznych modeli.

Omówienie wzoru

Jak zatem posługiwać się klauzulą WITH? Omówmy to na poniższym przykładzie.

WITH TempQuery AS (
SELECT
col1, col2, col3, SUM( col4 ) AS Attr1
FROM tab1
GROUP BY col1, col2, col3
)

SELECT
col1, col2, col3, Attr1
FROM TempQuery
WHERE Attr1 > 100

Powyższe zapytanie tworzy tymczasowy zbiór danych nazywany TempQuery, który zawiera sumę wartości zgrupowaną według trzech innych kolumn. Następnie w zapytaniu SELECT odwołujemy się do tego zapytania filtrując wartość atrybutu attr1 dla wartości większej niż 100. Oczywiście jest to tylko prosty przykład, jednakże nie zapominajmy, że tymczasowe zapytania możemy łączyć również z innymi funkcjami.

WITH ActualQuery AS(
SELECT
col1, col2, col3 SUM(col4) AS attr1
FROM ActualTab
GROUP BY col1, col2, col3
),
ArchivedQuery AS(
SELECT
col1, col2, col3, SUM(col4) AS attr1
FROM ArchivedTab
GROUP BY col1, col2, col3
)
SELECT * FROM ActualQuery
UNION
SELECT * FROM ArchivedQuery

Powyższe zapytanie połączy ze sobą dwie tabele według tej samej struktury.

Podsumowanie

W artykule przestawiłem klauzulę WITH, a także omówiłem jej zastosowanie. Następnie omówiłem zalety korzystania z tego wyrażenia i przedstawiłem praktyczne przykłady zastosowania.