CTE (Common Table Expression), määritelty WITH-lauseella, on nimetty tilapäinen tulosjoukko, johon voit viitata kyselyssä. CTE:t tekevät monimutkaisista kyselyistä luettavampia jakamalla ne nimetyiksi, loogisiksi vaiheiksi — ja ne mahdollistavat rekursiiviset kyselyt (hierarkkioille).
active_users (
id, name users active
)
active_users
name ;
WITH ... AS (...) määrittelee CTE:n — nimetyn kyselyn, johon viitataan myöhemmin. Se on kuin alakysely, mutta nimetty ja luettava, erityisesti monimutkaisissa kyselyissä.
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
Useat CTE:t hajottavat monimutkaisen kyselyn luettaviksi, peräkkäisiksi vaiheiksi — jokainen rakentuu edellisen pohjalle. Tämä on paljon selkeämpää kuin syvään sisäkkäiset alkyselyt.
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
Rekursiiviset CTE:t (WITH RECURSIVE) kulkevat hierarkkisen/puumaisen datan läpi (organisaatiokaaviot, kategoriapuut, koostumusluettelo, graafin polut) — jotain, joka on muuten hyvin vaikeaa SQL:ssä.
CTE:t ovat tärkeä SQL-ominaisuus luettavuudelle ja rekursiivisuudelle, ja niiden ymmärtäminen on arvokasta ylläpidettävien monimutkaisten kyselyjen kirjoittamiseen ja hierarkkisen datan käsittelyyn.
Primäärinen jokapäiväinen etu on luettavuus: monimutkaiset kyselyt, joissa on useita alakyselytasoja, tulevat vaikeiksi lukea ja ylläpitää, kun taas CTE:t antavat sinulle mahdollisuuden jakaa kysely nimetyiksi, peräkkäisiksi loogisiksi vaiheiksi (lue ylhäältä alas sarjana operaatioita), mikä parantaa selkeyttä dramaattisesti — tästä syystä CTE:t valitaan usein syvään sisäkkäisten alakyselyjen sijasta monimutkaisissa kyselyissä, mikä tekee niistä tärkeän työkalun ylläpidettävän SQL:n kirjoittamiseen.
Yhtä tärkeä on rekursiivinen CTE -kapasiteetti (WITH RECURSIVE), joka mahdollistaa hierarkkisen ja puumaisen datan kysely (organisaatiokaaviot, kategoriapuut, koostumusluettelo, graafin läpikulku) — kyselyn luokka, joka on muuten erittäin vaikeaa tai mahdotonta vakio-SQL:ssä, mikä tekee rekursiivisista CTE:istä standardiratkaisun hierarkkisen datan läpikulkuun.
Molemmat käyttötavat — CTE:t monimutkaisten kyselyjen hajottamiseen luettaviksi vaiheiksi ja rekursiiviset CTE:t hierarkkisen läpikulun kannalta — kattavat käytännön arvon.
Koska monimutkaiset kyselyt ovat yleisiä oikeissa sovelluksissa (ja luettavuus/ylläpidettävyys on tärkeää), ja koska hierarkkinen data (jota rekursiiviset CTE:t käsittelevät yksilöllisen hyvin) esiintyy usein (organisaatiorakenteet, sisäkkäiset kategoriat, johdettu data), CTE:iden ymmärtäminen on arvokasta senior-tason tietoa, joka parantaa kyselyn ylläpidettävyyttä ja avaa hierarkkisen kyselykyvyn — usein relevantti työkalu puhtaan, kehittyneen SQL:n kirjoittamiseen ja yleinen aihe, joka osoittaa kehittyneen SQL-kyvyn, erityisesti rekursiivinen muoto puumaista dataa varten.