Usare le espressioni di tabella comuni
Common Table Expressions (CTEs) forniscono un meccanismo che consente di definire una sottoquery che può quindi essere usata altrove in una query. A differenza di una tabella derivata, un CTE viene definito all'inizio di una query e può essere fatto riferimento più volte nella query esterna.
Le CTE sono espressioni denominate definite in una query. Analogamente alle sottoquery e alle tabelle derivate, le CTE forniscono un mezzo per suddividere i problemi di query in unità più piccole e modulari. L'ambito delle CTE è limitato all'esecuzione della query esterna. Eseguita la query esterna, termina anche la durata della CTE.
Scrivere query con CTE per recuperare i risultati
È possibile usare le CTE per recuperare i risultati. Per creare un CTE, definirlo in una clausola WITH, in base alla sintassi seguente:
WITH <CTE_name>
AS (<CTE_definition>)
Ad esempio, per usare un CTE per recuperare informazioni sugli ordini effettuati per anno da clienti distinti, è possibile eseguire la query seguente:
WITH CTE_year
AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;
Si assegna un nome alla CTE (named CTE_year) usando la clausola WITH, quindi si usa AS () per definire la sottoquery. Si fa quindi riferimento alla CTE risultante nella query esterna, che in questo caso viene eseguita nell'istruzione SELECT finale (FROM CTE_year). Il risultato sarà simile al seguente:
| orderyear | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Quando si scrivono query con CTE, tenere presenti le linee guida seguenti:
- Le CTE richiedono un nome per l'espressione di tabella, oltre ai nomi univoci per ognuna delle colonne a cui si fa riferimento nella clausola SELECT di CTE.
- Le CTE possono usare alias inline o esterni per le colonne.
- A differenza di una tabella derivata, un CTE può essere fatto riferimento più volte nella stessa query con una sola definizione. È anche possibile definire più CTE nella stessa clausola WITH.
- Le CTE supportano la ricorsione, in cui l'espressione viene definita con un riferimento a se stesso. Le CTE ricorsive non rientrano nell'ambito di questo modulo.