Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

WITHClause

Une WITH clause est une clause facultative qui précède la SELECT liste dans une requête. La WITH clause définit une ou plusieurs common_table_expressions. Chaque expression de table commune (CTE) définit une table temporaire, similaire à une définition de vue. Vous pouvez faire référence à ces tables temporaires dans la FROM clause. Elles ne sont utilisées que pendant l’exécution de la requête à laquelle elles appartiennent. Chaque CTE WITH clause spécifie un nom de table, une liste facultative de noms de colonnes et une expression de requête qui correspond à une table (une SELECT instruction).

WITHles sous-requêtes de clause constituent un moyen efficace de définir des tables qui peuvent être utilisées tout au long de l'exécution d'une seule requête. Dans tous les cas, les mêmes résultats peuvent être obtenus en utilisant des sous-requêtes dans le corps principal de l'SELECTinstruction, mais les sous-requêtes de WITH clause peuvent être plus simples à écrire et à lire. Dans la mesure du possible, les sous-requêtes de WITH clauses référencées plusieurs fois sont optimisées en tant que sous-expressions courantes ; il est donc possible d'évaluer une WITH sous-requête une seule fois et de réutiliser ses résultats. (Notez que les sous-expressions courantes ne sont pas limitées à celles définies dans la WITH clause.)

Syntaxe

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

où common_table_expression peut être non récursive. Voici la forme non-récursive :

CTE_table_name AS ( query )

Paramètres

Notes d’utilisation

Vous pouvez utiliser une WITH clause dans l'SQLinstruction suivante :

Si la FROM clause d'une requête contenant une WITH clause ne fait référence à aucune des tables définies par la WITH clause, la WITH clause est ignorée et la requête s'exécute normalement.

Une table définie par une sous-requête de WITH clause ne peut être référencée que dans le cadre de la SELECT requête à laquelle la WITH clause commence. Par exemple, vous pouvez faire référence à une telle table dans la FROM clause d'une sous-requête de la SELECT liste, de la WHERE clause ou de la HAVING clause. Vous ne pouvez pas utiliser une WITH clause dans une sous-requête et référencer sa table dans la FROM clause de la requête principale ou d'une autre sous-requête. Ce modèle de requête génère un message d'erreur sous la forme relation table_name doesn't exist de la table de WITH clauses.

Vous ne pouvez pas spécifier une autre WITH clause dans une sous-requête de WITH clause.

Vous ne pouvez pas faire de références directes à des tables définies par des sous-requêtes de WITH clause. Par exemple, la requête suivante renvoie une erreur en raison de la référence future à la table W2 dans la définition de table W1 :

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

Exemples

L'exemple suivant montre le cas le plus simple possible d'une requête contenant une WITH clause. La WITH requête nommée VENUECOPY sélectionne toutes les lignes de la VENUE table. La requête principale sélectionne à son tour toutes les lignes deVENUECOPY. La VENUECOPY table n'existe que pendant la durée de cette requête.

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'exemple suivant montre une WITH clause qui produit deux tables, nommées VENUE _ SALES et TOP _VENUES. La deuxième table de WITH requêtes effectue une sélection parmi la première. À son tour, la WHERE clause du bloc de requête principal contient une sous-requête qui limite la table 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)

Les deux exemples suivants illustrent les règles relatives à l'étendue des références de table basées sur des sous-requêtes de WITH clauses. La première requête s’exécute, mais la deuxième échoue avec une erreur prévue. La première requête contient une sous-requête de WITH clause dans la SELECT liste de la requête principale. La table définie par la WITH clause (HOLIDAYS) est référencée dans la FROM clause de la sous-requête de la SELECT liste :

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 deuxième requête échoue car elle tente de référencer la HOLIDAYS table dans la requête principale ainsi que dans la sous-requête de SELECT liste. Les références de requête principale sont hors de portée.

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