Common Table Expressions (CTE) je konstrukce, umožňující definovat a následně, v rámci působnosti jednoho dotazu, použít množinu záznamů. Jde tedy o obdobu běžně používaných poddotazů. Výsledek CTE se chová jako normální tabulka a lze s ním také tak pracovat.

Struktura CTE

SQL:
WITH nazev_CTE [( seznam_sloupcu )]
AS
( definice_vnitrniho_dotazu )
vnejsi_dotaz_pouzivajici_data_z nazev_CTE

Seznam sloupců v definici CTE není povinný. Pokud není uveden, budou ve výsledku dostupné všechny sloupce uvedené v definici vnitřního dotazu. Pokud chcete sloupce vypsat (například kvůli přejmenování), je potřeba aby se jejich počet shodoval s počtem sloupců uvedených v definici vnitřního dotazu. Jako vnější dotaz, tedy ten, který využívá výsledná data vnitřního dotazu, je možné použít SELECT, INSERT, UPDATE, DELETE, nebo CREATE VIEW.

Stejně jako může dotaz obsahovat více poddotazů, lze použít také více CTE. Celou definici pak uvozuje klíčové slovo WITH a jednotlivé vnitřní dotazy jsou odděleny čárkou.

SQL:
WITH
CTE1
    AS
    (
        SELECT klic, s1 FROM tabulka
    ),
CTE2
    AS
    (
        SELECT klic, s2 FROM tabulka
    )
SELECT s1, s2 FROM CTE1, CTE2
WHERE CTE1.klic = CTE2.klic

Každý vnitřní dotaz může také pracovat s výsledkem vnitřních dotazů uvedených před ním.

SQL:
WITH
CTE1
    AS
    (
        SELECT s1, s2, s3 FROM tabulka
    ),
CTE2
    AS
    (
        SELECT s2, s3 FROM CTE1
    )
SELECT s3 FROM CTE2

Mazání duplicitních záznamů

Pěkný příklad na použití CTE je odstranění duplicitních záznamů z tabulky.

Vytvoření ukázkové tabulky a naplnění daty s duplicitními záznamy:

SQL:
CREATE TABLE duplicity(
    sloupec1 int NOT NULL,
    sloupec2 nchar(10) NOT NULL
)
GO
INSERT duplicity VALUES (1, 'jedna')
INSERT duplicity VALUES (1, 'jedna')
INSERT duplicity VALUES (2, 'dve')
INSERT duplicity VALUES (2, 'tri')
INSERT duplicity VALUES (2, 'tri')
INSERT duplicity VALUES (3, 'tri')
INSERT duplicity VALUES (3, 'tri')
INSERT duplicity VALUES (3, 'tri')
GO

K nalezení a rozlišení duplicitních záznamů použijeme funkci ROW_NUMBER(), která v rámci definovaného bloku očísluje jednotlivé záznamy, které dotaz vrátí. Rozdělení na bloky záznamů (z nichž každý bude číslován zvlášť) zajišťuje klauzule OVER. Ta v našem případě říká, že blok bude určovat dvojice (sloupec1, sloupec2), respektive hodnoty v těchto sloupcích.

SQL:
SELECT sloupec1, sloupec2,
    ROW_NUMBER() OVER
        (
            PARTITION BY sloupec1, sloupec2 ORDER BY sloupec1
        ) AS cisloKopie
    FROM duplicity
GO

Výstup tohoto dotazu pak bude vypadat následovně:
Očíslované záznamy

Pokud tento dotaz použijeme jako vnitřní dotaz CTE, můžeme jednoduchou podmínkou ve vnějším dotazu odstranit všechny duplicitní záznamy.

SQL:
WITH CTE (s1, s2, cisloKopie)
AS
(
    SELECT sloupec1, sloupec2,
    ROW_NUMBER() OVER
        (
            PARTITION BY sloupec1, sloupec2 ORDER BY sloupec1
        ) AS cisloKopie
    FROM duplicity
)
DELETE FROM CTE
WHERE cisloKopie> 1
GO

SQL:
SELECT * FROM duplicity

Tabulka po odstranění duplicitních záznamů

Poměrně jednoduše jsme tak díky odvozenému sloupci smazali záznamy, které od sebe v původní tabulce nešly odlišit.

Díky větší přehlednosti se u rozsáhlejších dotazů vyplatí CTE použít i ve chvíli, kdy lze použít klasický poddotaz. Často zmiňovaná bývá také možnost vytvářet pomocí CTE rekurzivní dotazy. O těch se možná ještě někdy zmíním, dnes už to však nebude :)