Appearance
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