Although SQL is a declarative language, it provides a way of implementing the logic of sequential execution of code or of reusing code.

Common table expressions (CTE) is a feature that makes it possible to define a subquery once, give it a name, and then use it at several places in the main query.

The simplified syntax diagram for CTE is as follows:

WITH <subquery name> AS (<subquery code>) [, ...]
SELECT <Select list> FROM <subquery name>;

In the preceding syntax diagram, subquery code is a query whose results will be used later in the primary query as if it was a real table. The subquery in parentheses after the AS keyword is a common table expression. It can also be called a substatement or an auxiliary statement. The query after the WITH block is the primary or main query. The whole statement itself is called a WITH query.

It is possible to use not only the SELECT statements in a CTE, but also the INSERT, UPDATE, or DELETE statements.
It is also possible to use several CTEs in one WITH query. Every CTE has its name defined before the keyword, AS. The main query can reference a CTE by its name. A CTE can also refer to another CTE by the name. A CTE can refer only to the CTEs that were defined before the referencing one.

The references to CTEs in the primary query can be treated as table names. In fact, PostgreSQL executes the CTEs only once, caches the results, and reuses them instead of executing subqueries each time they occur in the main query. This makes them similar to tables.

CTEs may help developers in organizing SQL code. Suppose we want to find car models of the cars in the sample database that are built after 2010, and find out what is the minimal number of owners for these cars. Consider the following code:

car_portal=> WITH pre_select AS
(
  SELECT car_id, number_of_owners, car_model_id 
    FROM car_portal_app.car WHERE manufacture_year >= 2010
),
joined_data AS
(
  SELECT car_id, make, model, number_of_owners
    FROM pre_select 
      INNER JOIN car_portal_app.car_model ON pre_select.car_model_id= car_model.car_model_id
),
minimal_owners AS (SELECT min(number_of_owners) AS min_number_of_owners FROM pre_select)
SELECT car_id, make, model, number_of_owners
  FROM joined_data INNER JOIN minimal_owners 
    ON joined_data.number_of_owners = minimal_owners.min_number_of_owners;
 car_id |      make     |    model   | number_of_owners
--------+---------------+------------+------------------
      2 | Opel          | Corsa      |                1
      3 | Citroen       | C3         |                1
     11 | Nissan        | GT-R       |                1
     36 | KIA           | Magentis   |                1
…
(25 rows)

In the example, the logical part of the query is presented as a sequence of actions--filtering in pre_select and then joining in joined_data. The other part, that is, calculating the minimal number of owners, is executed in a dedicated subquery, minimal_owners. This makes the implementation of the query logic similar to that of an imperative programming language.

The use of CTEs in the preceding example does not make the whole query faster. However, there are situations where the use of CTEs can increase performance. Moreover, sometimes it is not possible to implement the logic in any other way except by CTEs. In the following sections, some of these situations are discussed in detail.

The order of execution of the CTEs is not defined. PostgreSQL aims to execute only the main query. If the main query contains references to the CTEs, then PostgreSQL will execute them first. If a SELECT CTE is not referenced by the main query, directly or indirectly, then it is not executed at all. Data-changing substatements are always executed.