Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Clausola WITH

Una clausola WITH è una clausola facoltativa che precede l'elenco SELECT in una query. La clausola WITH definisce uno o più common_table_expression. Ogni espressione comune di tabella (CTE) definisce una tabella temporanea, che è simile a una definizione di vista. È possibile fare riferimento a queste tabelle temporanee nella clausola FROM. Vengono utilizzati solo durante l'esecuzione della query a cui appartengono. Ogni CTE nella clausola WITH specifica un nome di tabella, un elenco facoltativo di nomi di colonna e un'espressione di query che restituisce una tabella (un'istruzione SELECT).

Le sottoquery della clausola WITH sono un modo efficace per definire le tabelle che possono essere utilizzate durante l'esecuzione di una singola query. In ogni caso, è possibile ottenere gli stessi risultati utilizzando le sottoquery nel corpo principale dell'istruzione SELECT, ma le sottoquery della clausola WITH potrebbero essere più semplici da scrivere e leggere. Ove possibile, le sottoquery della clausola WITH che sono referenziate più volte sono ottimizzate come sottoespressioni comuni; vale a dire, potrebbe essere possibile valutare una sottoquery WITH una volta e riutilizzarne i risultati. Tieni presente che le sottoespressioni comuni non sono limitate a quelle definite nella clausola WITH.

Sintassi

[ WITH  common_table_expression [, common_table_expression , ...] ]

dove common_table_expression può essere non ricorsivo. Di seguito è riportata la forma non ricorsiva:

CTE_table_name AS ( query )

Parametri

Note per l'utilizzo

È possibile utilizzare una clausola WITH nella seguente istruzione SQL:

Se la clausola FROM di una query che contiene una clausola WITH non fa riferimento a nessuna delle tabelle definite dalla clausola WITH, la clausola WITH viene ignorata e la query viene eseguita normalmente.

A una tabella definita da una sottoquery della clausola WITH è possibile fare riferimento solo nell'ambito della query SELECT avviata dalla clausola WITH. Ad esempio, puoi fare riferimento a una tabella di questo tipo nella clausola FROM di una sottoquery nell'elenco SELECT, nella clausola WHERE o nella clausola HAVING. Non puoi utilizzare una clausola WITH in una sottoquery e fare riferimento alla tabella nella clausola FROM della query principale o un'altra sottoquery. Questo modello di query genera un messaggio di errore nel formato relation table_name doesn't exist per la tabella della clausola WITH.

Non puoi specificare un'altra clausola WITH all'interno di una sottoquery della clausola WITH.

Non puoi creare riferimenti alle tabelle definite dalle sottoquery della clausola WITH. Ad esempio, la seguente query restituisce un errore a causa del riferimento in avanti alla tabella W2 nella definizione della tabella W1:

with w1 as (select * from w2), w2 as (select * from w1)
select * from sales;
ERROR:  relation "w2" does not exist

Esempi

L'esempio seguente mostra il caso più semplice possibile di una query che contiene una clausola WITH. La query WITH denominata VENUECOPY seleziona tutte le righe dalla tabella VENUE. La query principale a sua volta seleziona tutte le righe da VENUECOPY. La tabella VENUECOPY esiste solo per la durata di questa query.

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

 venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |          0
2 | Columbus Crew Stadium      | Columbus        | OH         |          0
3 | RFK Stadium                | Washington      | DC         |          0
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |          0
5 | Gillette Stadium           | Foxborough      | MA         |      68756
6 | New York Giants Stadium    | East Rutherford | NJ         |      80242
7 | BMO Field                  | Toronto         | ON         |          0
8 | The Home Depot Center      | Carson          | CA         |          0
9 | Dick's Sporting Goods Park | Commerce City   | CO         |          0
v     10 | Pizza Hut Park             | Frisco          | TX         |          0
(10 rows)

L'esempio seguente mostra una clausola WITH che produce due tabelle, denominate VENUE_SALES e TOP_VENUES. La seconda tabella della query WITH seleziona dalla prima. A sua volta, la clausola WHERE del blocco di query principale contiene una sottoquery che vincola la tabella TOP_VENUES.

with venue_sales as
(select venuename, venuecity, sum(pricepaid) as venuename_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
group by venuename, venuecity),

top_venues as
(select venuename
from venue_sales
where venuename_sales > 800000)

select venuename, venuecity, venuestate,
sum(qtysold) as venue_qty,
sum(pricepaid) as venue_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
and venuename in(select venuename from top_venues)
group by venuename, venuecity, venuestate
order by venuename;

        venuename       |   venuecity   | venuestate | venue_qty | venue_sales
------------------------+---------------+------------+-----------+-------------
August Wilson Theatre   | New York City | NY         |      3187 |  1032156.00
Biltmore Theatre        | New York City | NY         |      2629 |   828981.00
Charles Playhouse       | Boston        | MA         |      2502 |   857031.00
Ethel Barrymore Theatre | New York City | NY         |      2828 |   891172.00
Eugene O'Neill Theatre  | New York City | NY         |      2488 |   828950.00
Greek Theatre           | Los Angeles   | CA         |      2445 |   838918.00
Helen Hayes Theatre     | New York City | NY         |      2948 |   978765.00
Hilton Theatre          | New York City | NY         |      2999 |   885686.00
Imperial Theatre        | New York City | NY         |      2702 |   877993.00
Lunt-Fontanne Theatre   | New York City | NY         |      3326 |  1115182.00
Majestic Theatre        | New York City | NY         |      2549 |   894275.00
Nederlander Theatre     | New York City | NY         |      2934 |   936312.00
Pasadena Playhouse      | Pasadena      | CA         |      2739 |   820435.00
Winter Garden Theatre   | New York City | NY         |      2838 |   939257.00
(14 rows)

I seguenti due esempi illustrano le regole per l'ambito dei riferimenti di tabella basati sulle sottoquery della clausola WITH. La prima query viene eseguita, ma la seconda non riesce con un errore previsto. La prima query contiene la sottoquery clausola WITH all'interno dell'elenco SELECT della query principale. Alla tabella definita dalla clausola WITH (HOLIDAYS) si fa riferimento nella clausola FROM della sottoquery nell'elenco SELECT:

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join date on sales.dateid=date.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

caldate   | daysales | dec25sales
-----------+----------+------------
2008-12-25 | 70402.00 |   70402.00
2008-12-31 | 12678.00 |   70402.00
(2 rows)

La seconda query non riesce perché tenta di fare riferimento alla tabella HOLIDAYS nella query principale e nella sottoquery elenco SELECT. I riferimenti della query principale sono fuori ambito.

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join holidays on sales.dateid=holidays.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

ERROR:  relation "holidays" does not exist