Narcis Miclaus

8 min de citit

SQL Server, de la zero Lecția 11 / 40

Subquery vs CTE: când folosești fiecare

Subquery-uri inline, tabele derivate, subquery-uri corelate și CTE-uri. Regula lizibilității, mitul „este același plan?” și cum refactorizezi un query monstruos.

Începi să scrii un query simplu la Runehold: „top 10 clienți după lifetime value”. Apoi marketingul cere „doar clienți din Germania”. Apoi „și doar cei al căror LTV este peste media țării”. Apoi „arată-mi atât trimestrul trecut, cât și pe acesta, unul lângă altul”. Dintr-odată, query-ul tău simplu are 60 de linii, este imbricat pe trei niveluri și nimeni nu îl mai poate citi.

Lecția aceasta este despre cum păstrezi astfel de query-uri lizibile. Vom acoperi subquery-urile (query-uri în interiorul altor query-uri), tabelele derivate (subquery-uri în FROM), subquery-urile corelate (subquery-uri care fac referire la rândul exterior) și CTE-urile (Common Table Expressions, alias clauzele WITH) — instrumentul care îți permite să descompui un query mare în pași denumiți și lizibili.

Cele patru tipuri de subquery

1. Subquery scalar: returnează o singură valoare

SELECT o.OrderId,
       o.Total,
       (SELECT AVG(Total) FROM Sales.Orders)  AS overall_average
FROM Sales.Orders AS o
WHERE o.Total > (SELECT AVG(Total) FROM Sales.Orders);

Query-ul interior returnează o singură valoare (un rând, o coloană). O poți folosi oriunde ar merge o valoare literală: în SELECT, în WHERE, în ORDER BY.

Atenție: dacă subquery-ul scalar returnează la runtime mai mult de un rând, primești o eroare. SQL Server nu știe ce valoare să folosească. Scrie subquery-uri scalare astfel încât să returneze demonstrabil un singur rând.

2. Subquery cu mai multe rânduri folosind IN sau EXISTS

-- Toate comenzile clienților din Italia
SELECT OrderId, Total
FROM Sales.Orders
WHERE CustomerId IN (SELECT CustomerId FROM Sales.Customer WHERE Country = 'IT');

-- Echivalent cu EXISTS (deseori mai clar pentru condiții complexe)
SELECT o.OrderId, o.Total
FROM Sales.Orders AS o
WHERE EXISTS (
    SELECT 1 FROM Sales.Customer c
    WHERE c.CustomerId = o.CustomerId AND c.Country = 'IT'
);

IN merge bine cu o coloană simplă. EXISTS este mai potrivit când subquery-ul are mai multe condiții sau trebuie să facă referire la rândul exterior. Ambele produc planuri aproape identice în SQL Server modern; alege pe baza lizibilității.

3. Tabel derivat: subquery în FROM

-- AOV per țară, filtrat pentru țări cu cel puțin 10 comenzi
SELECT country, orders, aov
FROM (
    SELECT CountryCode AS country,
           COUNT(*)    AS orders,
           AVG(Total)  AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
) AS country_stats
WHERE orders >= 10
ORDER BY aov DESC;

Subquery-ul (SELECT ... GROUP BY CountryCode) este un tabel derivat. Trebuie să primească un alias (aici, country_stats), iar query-ul exterior îl tratează ca pe orice alt tabel. Util când trebuie să agregi o dată și apoi să filtrezi/joini pe rezultatul agregat.

4. Subquery corelată: face referire la rândul exterior

-- Pentru fiecare client, arată data ultimei sale comenzi
SELECT c.CustomerId,
       c.Name,
       (SELECT MAX(o.OrderDate)
        FROM Sales.Orders o
        WHERE o.CustomerId = c.CustomerId) AS last_order
FROM Sales.Customer AS c;

Query-ul interior face referire la c.CustomerId, care provine din query-ul exterior. Conceptual, rulează o dată pentru fiecare rând exterior. Aceasta se numește subquery corelată.

Subquery-urile corelate par lente, dar optimizatorul le rescrie de obicei într-un join sau semi-join. Totuși, preferă join-uri explicite când este posibil — exprimă mai clar intenția.

CTE: clauza WITH

Un CTE (Common Table Expression) este un subquery denumit pe care îl definești la începutul instrucțiunii și la care faci referire mai târziu:

WITH country_stats AS (
    SELECT CountryCode AS country,
           COUNT(*)    AS orders,
           AVG(Total)  AS aov,
           SUM(Total)  AS revenue
    FROM Sales.Orders
    GROUP BY CountryCode
),
top_customers AS (
    SELECT TOP (10) c.Country, c.Name, SUM(o.Total) AS ltv
    FROM Sales.Customer AS c
    JOIN Sales.Orders   AS o ON o.CustomerId = c.CustomerId
    GROUP BY c.Country, c.Name
    ORDER BY ltv DESC
)
SELECT cs.country, cs.revenue, cs.aov, tc.Name AS top_customer, tc.ltv
FROM country_stats AS cs
LEFT JOIN top_customers AS tc ON tc.Country = cs.country
ORDER BY cs.revenue DESC;

Compară cu aceeași logică scrisă cu subquery-uri imbricate — ar fi 30 de linii de imbricări, ilizibile după o săptămână. CTE-urile transformă query-uri mari în pași denumiți, fiecare un gând autosuficient.

Reguli pentru CTE:

  • Mai multe CTE-uri se separă prin virgulă.
  • Fiecare CTE poate face referire la CTE-uri anterioare din același WITH.
  • CTE-ul există doar pentru această unică instrucțiune. Nu este un view. Nu se stochează.
  • CTE-urile sunt în general inlining-uite de optimizator — fără penalizare de performanță față de un tabel derivat.

Când să folosești un CTE:

  • Orice query care ar imbrica subquery-uri pe două sau mai multe niveluri.
  • Orice query pe care l-ai comenta cu „pasul 1”, „pasul 2”.
  • Query-uri recursive (vezi secțiunea următoare).

Mitul „este același plan”

Vei auzi oameni spunând „CTE-urile sunt la fel ca tabelele derivate, același plan, aceeași performanță”. În SQL Server este în mare parte adevărat: optimizatorul face de obicei inlining unui CTE non-recursiv și îl tratează la fel ca pe un tabel derivat.

Nu mereu. Un CTE referit de mai multe ori nu este materializat; optimizatorul face inlining definiției la fiecare referință. Pentru CTE-uri costisitoare folosite de mai multe ori, asta înseamnă că operația costisitoare rulează de fiecare dată. Dacă vezi acest tipar și CTE-ul este într-adevăr costisitor, soluția este să arunci rezultatele CTE-ului într-un tabel temporar și să joini cu el.

-- Dacă country_stats este foarte costisitor și îl referim de două ori:
DROP TABLE IF EXISTS #country_stats;
SELECT CountryCode AS country,
       COUNT(*) AS orders,
       AVG(Total) AS aov
INTO #country_stats
FROM Sales.Orders
GROUP BY CountryCode;

-- Apoi query-urile exterioare folosesc #country_stats, calculat o singură dată.
SELECT * FROM #country_stats WHERE orders > 100;
SELECT * FROM #country_stats WHERE aov < 50;

Tabelele temporare (prefixul #) trăiesc cât durează sesiunea ta. Se materializează; optimizatorul le vede statisticile; pot fi chiar indexate. Pentru calcule intermediare cu adevărat costisitoare, câștigă în fața CTE-urilor. Pentru toate celelalte cazuri, CTE-urile sunt mai curate.

CTE recursive: mai puțin înfricoșătoare decât par

Un CTE recursiv se referă la sine. Utilizare clasică: parcurgerea unei ierarhii, ca organigrama Runehold (angajați → manageri → directori → VP-uri).

WITH org_tree AS (
    -- Ancora: CEO-ul, vârful arborelui
    SELECT EmployeeId, FullName, ManagerId, 0 AS level
    FROM HR.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    -- Partea recursivă: adaugă rânduri cu un nivel mai jos față de ce avem deja
    SELECT e.EmployeeId, e.FullName, e.ManagerId, t.level + 1
    FROM HR.Employee AS e
    JOIN org_tree    AS t ON t.EmployeeId = e.ManagerId
)
SELECT level, REPLICATE('  ', level) + FullName AS indented_name
FROM org_tree
ORDER BY level, FullName;

Structura:

  1. Ancora — punctul de pornire. „Rândurile unde ManagerId este NULL” = CEO-ul.
  2. UNION ALL — obligatoriu; nu UNION.
  3. Partea recursivă — un query care joinează CTE-ul cu el însuși, adăugând câte un strat la fiecare iterație.

Motorul rulează mai întâi ancora, apoi partea recursivă în mod repetat, adăugând rânduri până când partea recursivă returnează zero rânduri noi. Eșuează în siguranță cu o limită implicită de 100 de niveluri de recursie; suprascrie cu OPTION (MAXRECURSION 500) dacă ai un arbore într-adevăr adânc.

Utilizări ale CTE-urilor recursive:

  • Organigrame, arbori de foldere, fire de comentarii, imbricarea categoriilor.
  • Bill-of-materials (produs → piese → sub-piese).
  • Generarea de intervale de date: „dă-mi un rând pentru fiecare zi între X și Y”.
  • Parcurgerea grafurilor.

Pentru orice este accesibil dintr-o singură „ancoră”, un CTE recursiv este instrumentul potrivit. Nu este magie; este doar self-join repetat cu o condiție de oprire.

Refactorizarea unui query monstruos

Iată un înainte și după. Versiunea „înainte” se bazează pe un query real din producție pe care l-am rescris anul trecut.

Înainte — imbricat, 55 de linii, imposibil de urmărit:

SELECT
    c.Name,
    c.Country,
    (SELECT COUNT(*) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS order_count,
    (SELECT SUM(o.Total) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS lifetime_value,
    (SELECT MAX(o.OrderDate) FROM Sales.Orders o
     WHERE o.CustomerId = c.CustomerId) AS last_order_date,
    CASE
        WHEN (SELECT COUNT(*) FROM Sales.Orders o
              WHERE o.CustomerId = c.CustomerId) = 0 THEN 'Never ordered'
        WHEN (SELECT MAX(o.OrderDate) FROM Sales.Orders o
              WHERE o.CustomerId = c.CustomerId) < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
        ELSE 'Active'
    END AS status
FROM Sales.Customer c
WHERE c.Country IN ('IT', 'NL', 'DE');

Patru subquery-uri corelate, fiecare rulând per client. CASE-ul repetă două dintre ele. Lent, ilizibil, fragil.

După — folosind un CTE:

WITH customer_orders AS (
    SELECT o.CustomerId,
           COUNT(*)       AS order_count,
           SUM(o.Total)   AS lifetime_value,
           MAX(o.OrderDate) AS last_order_date
    FROM Sales.Orders AS o
    GROUP BY o.CustomerId
)
SELECT
    c.Name,
    c.Country,
    COALESCE(co.order_count, 0)     AS order_count,
    COALESCE(co.lifetime_value, 0)  AS lifetime_value,
    co.last_order_date,
    CASE
        WHEN co.order_count IS NULL OR co.order_count = 0 THEN 'Never ordered'
        WHEN co.last_order_date < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
        ELSE 'Active'
    END AS status
FROM Sales.Customer AS c
LEFT JOIN customer_orders AS co ON co.CustomerId = c.CustomerId
WHERE c.Country IN ('IT', 'NL', 'DE');

O singură agregare, joinată o singură dată, folosită de patru ori. Același rezultat. Mai rapid. Lizibil. Mai ușor de extins cu o coloană nouă.

Acest refactor este de departe cel mai frecvent pe care îl fac în cod real. Dacă vezi un query cu același subquery scalar de cinci ori, treci imediat la un CTE.

Rulează asta pe propriul tău server

USE Runehold;
GO

-- 1. Subquery scalar: comenzi peste media generală
SELECT o.OrderId, o.Total,
       (SELECT AVG(Total) FROM Sales.Orders) AS overall_aov
FROM Sales.Orders AS o
WHERE o.Total > (SELECT AVG(Total) FROM Sales.Orders)
ORDER BY o.Total DESC;

-- 2. Tabel derivat: țări cu cel puțin 2 comenzi, cu AOV-ul lor
SELECT country, orders, aov
FROM (
    SELECT CountryCode AS country, COUNT(*) AS orders, AVG(Total) AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
) AS s
WHERE orders >= 2
ORDER BY aov DESC;

-- 3. Versiunea cu CTE a aceluiași query
WITH country_stats AS (
    SELECT CountryCode, COUNT(*) AS orders, AVG(Total) AS aov
    FROM Sales.Orders
    GROUP BY CountryCode
)
SELECT CountryCode, orders, aov
FROM country_stats
WHERE orders >= 2
ORDER BY aov DESC;

-- 4. Multi-CTE: clasificarea clienților
WITH customer_orders AS (
    SELECT CustomerId,
           COUNT(*)     AS order_count,
           SUM(Total)   AS ltv,
           MAX(OrderDate) AS last_order
    FROM Sales.Orders
    GROUP BY CustomerId
),
classified AS (
    SELECT c.CustomerId, c.Name, c.Country,
           COALESCE(co.ltv, 0) AS ltv,
           CASE
              WHEN co.order_count IS NULL THEN 'Never ordered'
              WHEN co.last_order  < DATEADD(MONTH, -6, GETDATE()) THEN 'Dormant'
              ELSE 'Active'
           END AS status
    FROM Sales.Customer    AS c
    LEFT JOIN customer_orders AS co ON co.CustomerId = c.CustomerId
)
SELECT * FROM classified ORDER BY ltv DESC;

-- 5. CTE recursiv: generează fiecare zi între două date
WITH days AS (
    SELECT CAST('2026-04-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d)
    FROM days
    WHERE d < '2026-04-10'
)
SELECT d, DATENAME(WEEKDAY, d) AS day_of_week
FROM days
OPTION (MAXRECURSION 1000);

Cel recursiv este deosebit de puternic: îți dă un rând pe dată fără un tabel-dimensiune de date. Util pentru a umple golurile dintr-un raport time series.

Lecția următoare: funcții de fereastră. ROW_NUMBER, LAG, LEAD, totaluri cumulative. Acel ceva care face „top N per grup” trivial și înlocuiește jumătate dintre cazurile în care oamenii ar apela la un self-join.

Caută