Så gör CTE:er SQL-frågor tydligare och lättare att följa

En rapportfråga börjar som en enda `SELECT`, men efter några nästlade delfrågor blir det svårt att se var resultatet faktiskt kommer ifrån. En CTE kan ge frågan tydliga, namngivna steg, men hur databasen kör dem beror på vilken SQL-motor du använder.

En CTE gör nästlade SQL-frågor lättare att läsa

När en underfråga får ett namn blir det enklare att förstå vad nästa steg bygger på. En CTE, eller Common Table Expression, är ett namngivet frågeresultat som hör till en enskild SQL-sats, inte en permanent tabell.

Anta att du vill visa webbplatsens försäljning per land och bara ta med länder över en viss gräns. Du kan först summera orderraderna och sedan filtrera summeringen:

WITH sales_by_country AS (
    SELECT country, SUM(order_total) AS revenue
    FROM orders
    GROUP BY country
)
SELECT country, revenue
FROM sales_by_country
WHERE revenue > 10000
ORDER BY revenue DESC;

Namnet `sales_by_country` gör rollen tydlig: den första frågan skapar en sammanställning, och den yttre frågan väljer ur den. Resultatet finns bara inom den här satsen, så en separat fråga kan inte använda namnet senare.

Flera CTE:er kan göra en rapport enklare att felsöka

När en rapport har flera beräkningssteg blir det frestande att pressa in allt i en enda fråga. Jag tycker att en CTE-kedja är ett bättre val när varje steg går att beskriva med ett tydligt namn och kontrollera för sig.

En webbplatsrapport kan till exempel först välja betalda ordrar, sedan summera intäkten per land och till sist visa de största marknaderna:

WITH paid_orders AS (
    SELECT country, order_total
    FROM orders
    WHERE status = 'paid'
),
sales_by_country AS (
    SELECT country, SUM(order_total) AS revenue
    FROM paid_orders
    GROUP BY country
)
SELECT country, revenue
FROM sales_by_country
WHERE revenue > 10000
ORDER BY revenue DESC;

Varje CTE efter den första kan använda föregående definition. I den här frågan går det därför att kontrollera filtreringen före summeringen, vilket ofta gör fel i rapportlogik lättare att hitta än i en lång nästlad fråga.

CTE betyder inte automatiskt att data sparas eller körs snabbare

Här finns en vanlig missuppfattning: en CTE ser ut som en tillfällig tabell, men namnet säger inte att databasen lagrar resultatet på det sättet. Exekveringen skiljer sig mellan databasmotorer, och en tydligare fråga blir inte av den anledningen automatiskt snabbare.

I SQL Server materialiseras CTE-resultat inte som standard; om samma CTE refereras flera gånger kan den definierande frågan köras om. Microsoft föreslår att man överväger ett tillfälligt objekt vid upprepade referenser. PostgreSQL har andra optimeringsregler: en enkel CTE som används en gång kan vanligtvis slås ihop med huvudfrågan, medan en CTE som används flera gånger normalt materialiseras. Microsoft Learn om CTE i Transact-SQL och PostgreSQLs dokumentation om WITH-frågor beskriver dessa skillnader.

Rekursiva CTE:er följer hierarkier steg för steg

En organisationslista lagrar ofta varje persons chef, inte hela rapporteringskedjan. En rekursiv CTE kan börja med en person och sedan hitta nästa nivå genom att åter använda sitt eget resultat.

WITH RECURSIVE team_tree AS (
    SELECT employee_id, manager_id, 0 AS level
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    SELECT e.employee_id, e.manager_id, t.level + 1
    FROM employees AS e
    JOIN team_tree AS t
      ON e.manager_id = t.employee_id
)
SELECT employee_id, manager_id, level
FROM team_tree
ORDER BY level;

Den första delen kallas ankardelen och hämtar startpersonen. Den andra delen hittar personer som rapporterar till rader från föregående nivå; arbetet fortsätter tills inga fler rader hittas. I PostgreSQL och MySQL skrivs `RECURSIVE` efter `WITH`, medan SQL Server inte använder det nyckelordet i CTE-syntaxen. SQL Servers dokumentation om rekursiva CTE:er beskriver ankardel och rekursiv del.

Rekursion behöver ett tydligt stoppvillkor. Om tabellen innehåller en cirkel i relationerna kan sökningen fortsätta oväntat länge; i SQL Server går det att sätta en gräns med `MAXRECURSION`, medan PostgreSQL erbjuder bland annat en `CYCLE`-klausul för cykeldetektering. Anpassa syntaxen till din databas och kontrollera att hierarkin verkligen ska omfatta alla nivåer.

Välj CTE, underfråga eller temporär tabell efter behov

Det finns ingen syntax som passar bäst för alla frågor. För ett enda tydligt beräkningssteg kan en underfråga räcka; när flera steg behöver namn blir en CTE ofta lättare att läsa; och när ett stort mellanresultat återanvänds kan en temporär tabell vara mer lämplig.

• Välj CTE när frågan har logiska steg eller behöver rekursion.
• Välj underfråga när beräkningen är liten och bara används på ett ställe.
• Testa temporär tabell när samma omfattande resultat behövs flera gånger eller när du vill inspektera mellanresultatet separat.

Om du bygger en SQL-rapport för exempelvis webbplatsens försäljning skulle jag välja CTE för ett par tydliga sammanställningssteg, men jämföra med en temporär tabell om den dyraste sammanställningen återanvänds. Då får du läsbar kod utan att anta att CTE-namnet i sig förbättrar körningen.

En läsbar fråga är lättare att kontrollera

Se CTE:n som ett sätt att ge delresultat begripliga namn, inte som en ny tabell eller prestandagaranti. Börja med en CTE när den gör logiken tydligare, och låt exekveringsplanen avgöra om upplägget också fungerar bra för din databas.