Skip to content

WITH Clause

Define common table expressions (CTEs) for reuse in queries. CTEs are defined once as subqueries in the WITH clause that precedes a SELECT query and are reusable throughout that query.

[ WITH name AS (subquery) [, ...] ]

Give each WITH subquery a unique name. The WITH clause may define one or more subqueries, using any supported SELECT statement syntax. CTEs cannot contain DML commands (INSERT, UPDATE, DELETE).

The following query is a simple example of a join between the match table and a subquery defined in the WITH clause (w1):

premdb=# with w1 as (select * from awayteam) 
select * from match, w1 
where w1.atid=match.atid;
 seasonid |      matchday       | htid | atid | ftscore | htscore | atid |          name           
----------+---------------------+------+------+---------+---------+------+-------------------------
       1 | 1992-08-01 00:00:00 |    2 |   52 | 0-1     | -       |   52 | Aston Villa
       1 | 1992-08-01 00:00:00 |    2 |   55 | 0-1     | -       |   55 | Blackburn Rovers
       1 | 1992-08-01 00:00:00 |    2 |   63 | 2-1     | -       |   63 | Chelsea
       1 | 1992-08-01 00:00:00 |    2 |   64 | 3-0     | -       |   64 | Coventry City
       1 | 1992-08-01 00:00:00 |    2 |   65 | 3-0     | -       |   65 | Crystal Palace
...

Parent topic:SELECT