WITH (CTE)

A CTE names a subquery with WITH so you can reuse it and read queries top to bottom.

In this page:

WITH (CTE)

Syntax

sql

WITH cte_name AS (
  SELECT columns FROM table_name WHERE condition
)
SELECT columns FROM cte_name;

WITH (CTE)

WITH name AS (SELECT ...) defines a temporary named result that the main query can reference, even several times. Multiple CTEs are separated by commas and can reference earlier ones.

CTEs make complex queries readable. Since PostgreSQL 12 they are inlined when referenced once, unless MATERIALIZED is specified.

Note: Use MATERIALIZED or NOT MATERIALIZED to control CTE optimisation in PostgreSQL 12 and later.

Example: WITH (CTE)

sql

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER);
INSERT INTO orders VALUES (1,'Ada',50),(2,'Bob',20),(3,'Ada',70),(4,'Cy',10),(5,'Bob',95);
WITH totals AS (SELECT customer, SUM(total) AS spent FROM orders GROUP BY customer),
     big AS (SELECT customer, spent FROM totals WHERE spent >= 100)
SELECT customer, spent FROM big ORDER BY spent DESC;

-- Output:
-- customer | spent
-- Ada | 120
-- Bob | 115

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson IN/NOT IN with subquery Next Lesson → Recursive CTEs

WITH (CTE)

WITH (CTE)

Chapter Quiz — Complete all 7 topics to unlock

Login to run this code