Docs/Querying/CTEs (WITH)
Querying

CTEs in BigQuery

A CTE is a named subquery in a WITH clause. BigQuery runs a non-recursive CTE once per reference, reads nothing for a CTE that is never used, and pushes outer filters into it, so partition pruning still works.

Practice this in free lessons on learn.querylab.io: Name a step with WITH and 1 more.

A common table expression (CTE) is a subquery with a name, written in a WITH clause in front of the query that uses it. It exists only for that one statement. BigQuery doesn’t store the result of a non-recursive CTE: Google’s reference says it runs once for each reference and is “useful primarily for readability”. You don’t pay for a CTE you never read, and a filter written outside a CTE still prunes partitions inside it.

The shape

Each CTE can read the ones defined before it, so a long query becomes a list of named steps:

two steps, then the final queryOpen in Querylab.io
12345678910111213
WITH orders AS (
  SELECT * FROM UNNEST([
    STRUCT(1 AS id, 'ana' AS customer, 30 AS amount),
    (2, 'ana', 25), (3, 'ben', 15), (4, 'cy', 80)
  ])
),
per_customer AS (
  SELECT customer, SUM(amount) AS spent FROM orders GROUP BY customer
)
SELECT customer, spent
FROM per_customer
WHERE spent >= 50
ORDER BY spent DESC
Result in BigQuery
Rowcustomerspent
1cy80
2ana55
2 rows

per_customer reads orders, and the final SELECT reads per_customer. Separate the CTEs with commas. There is no comma before the final query, and the final query is required.

What a CTE costs

Bytes are counted on the tables the whole statement reads. A CTE that nothing reads is removed, so this estimate is 0 bytes even though it says SELECT *:

a CTE that nothing reads
1234
WITH all_words AS (
  SELECT * FROM `bigquery-public-data.samples.shakespeare`
)
SELECT 'all_words is never read' AS answer
BigQuery dry run

This query will process 0 B when run.

Reading a CTE twice doesn’t double the estimate. Both of these queries process 1,317,248 bytes:

one reference
1234
WITH words AS (
  SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare`
)
SELECT SUM(word_count) AS total FROM words
BigQuery dry run

This query will process 1.3 MiB when run.

two references, same bytes
123456
WITH words AS (
  SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare`
)
SELECT
  (SELECT SUM(word_count) FROM words) AS total,
  (SELECT MAX(word_count) FROM words) AS biggest
BigQuery dry run

This query will process 1.3 MiB when run.

The bill stays the same, but the work doesn’t. With two references, the CTE runs twice and uses slot time twice. If the CTE is an expensive join and several steps read it, compute it once in a temp table instead. On capacity pricing, where you pay for slots, that matters more than the bytes.

Two references can disagree

Because each reference runs separately, a non-deterministic function gives each one its own value. Both sides of this comparison read the same CTE, one row, and the values differ:

RAND() in a CTE read twice
123
WITH picked AS (SELECT RAND() AS value)
SELECT a.value = b.value AS same_value
FROM picked AS a CROSS JOIN picked AS b
Result in BigQuery
Rowsame_value
1false
1 row

The same applies to GENERATE_UUID(). If two parts of a query must see the same random values, write them to a temp table first.

Filters reach inside a CTE

A CTE is not a barrier for the optimizer. pageviews_2020 is partitioned by datehour, and these two queries process the same 5.5 GiB:

date filter outside the CTE
1234567
WITH views AS (
  SELECT title, views, datehour
  FROM `bigquery-public-data.wikipedia.pageviews_2020`
)
SELECT title, views
FROM views
WHERE datehour >= '2020-06-01' AND datehour < '2020-06-02'
BigQuery dry run

This query will process 5.5 GiB when run.

date filter inside the CTE
123456
WITH views AS (
  SELECT title, views
  FROM `bigquery-public-data.wikipedia.pageviews_2020`
  WHERE datehour >= '2020-06-01' AND datehour < '2020-06-02'
)
SELECT title, views FROM views
BigQuery dry run

This query will process 5.5 GiB when run.

So you can keep a general CTE at the top and filter later. Column pruning works the same way: only the columns the final query needs are read, so SELECT * inside a CTE costs nothing extra when the outer query names its columns. Check this with the estimate rather than assuming it, because a filter can’t move past an aggregation or a window function that needs the other rows. In Querylab.io the status bar estimate updates as you type.

On a table with require_partition_filter, every reference needs its own filter. A self-join of a CTE was refused with WHERE a.d = '2024-01-05' and passed with AND b.d = '2024-01-05' added.

Order and scope

A CTE can only read CTEs defined above it. Referring to a later one fails, and the message talks about a table, because BigQuery looks for a real table with that name:

reading a CTE defined laterBigQuery rejects this query
123
WITH totals AS (SELECT SUM(amount) AS total FROM orders),
orders AS (SELECT 10 AS amount UNION ALL SELECT 20)
SELECT total FROM totals
Error in BigQuery

Table "orders" must be qualified with a dataset (e.g. dataset.table).

Adding the RECURSIVE keyword changes that. With WITH RECURSIVE, CTEs in the clause can see each other in any order, even when none of them is recursive:

same query with WITH RECURSIVE
123
WITH RECURSIVE totals AS (SELECT SUM(amount) AS total FROM orders),
orders AS (SELECT 10 AS amount UNION ALL SELECT 20)
SELECT total FROM totals
Result in BigQuery
Rowtotal
130
1 row

Each name can appear once per WITH clause:

the same name twiceBigQuery rejects this query
123
WITH orders AS (SELECT 1 AS id),
orders AS (SELECT 2 AS id)
SELECT id FROM orders
Error in BigQuery

Duplicate alias orders for WITH subquery at [2:1]

A subquery can have its own WITH, and its names hide the outer ones inside that subquery only:

an inner CTE hides an outer oneOpen in Querylab.io
1234
WITH totals AS (SELECT 'outer' AS source)
SELECT
  (WITH totals AS (SELECT 'inner' AS source) SELECT source FROM totals) AS in_subquery,
  (SELECT source FROM totals) AS at_top_level
Result in BigQuery
Rowin_subqueryat_top_level
1innerouter
1 row

A CTE also hides a real table with the same unqualified name, which is handy for testing a query against inline data. A CTE ends with its statement, so the second query in a script can’t see it:

a CTE doesn't reach the next statementBigQuery rejects this query
123
WITH orders AS (SELECT 1 AS id)
SELECT id FROM orders;
SELECT id FROM orders
Error in BigQuery

Invalid value: Table "orders" must be qualified with a dataset (e.g. dataset.table). at [3:1]

Syntax from other databases

Three habits from Postgres and SQL Server fail here. BigQuery has no column list after the name, so alias the columns inside the query:

a column list after the CTE nameBigQuery rejects this query
12
WITH totals (customer, spent) AS (SELECT 'ana', 55)
SELECT customer, spent FROM totals
Error in BigQuery

Syntax error: Expected ")" but got identifier "customer" at [1:14]

It has no MATERIALIZED hint, because it never stores a non-recursive CTE:

the MATERIALIZED keywordBigQuery rejects this query
12
WITH totals AS MATERIALIZED (SELECT 'ana' AS customer, 55 AS spent)
SELECT customer, spent FROM totals
Error in BigQuery

Syntax error: Expected "(" but got keyword MATERIALIZED at [1:16]

And WITH can’t sit in front of INSERT:

WITH before INSERTBigQuery rejects this query
1234
CREATE TEMP TABLE big_orders (id INT64);
WITH orders AS (SELECT 1 AS id)
INSERT INTO big_orders SELECT id FROM orders;
SELECT id FROM big_orders
Error in BigQuery

Syntax error: Unexpected keyword INSERT at [3:1]

It belongs to the query, so it goes after INSERT INTO. The same placement applies to CREATE TABLE ... AS WITH ... and to the source of a MERGE:

WITH inside INSERTOpen in Querylab.io
12345
CREATE TEMP TABLE big_orders (id INT64);
INSERT INTO big_orders
WITH orders AS (SELECT 1 AS id)
SELECT id FROM orders;
SELECT id FROM big_orders
Result in BigQuery
Rowid
11
1 row

CTE, view or temp table

You needUse
Named steps in one statementCTE
A result read by several statements in a scriptTemp table
Expensive work reused inside one statementTemp table, so it runs once
The same logic in many queriesView
A precomputed result that stays freshMaterialized view
Hierarchies of unknown depthRecursive CTE

Recursive CTEs are the exception to the storage rule: BigQuery materializes their results. Pipe syntax is another way to write a query as steps, and it can follow a WITH clause.

Open the playground. It is the real app on public datasets and needs no sign-in.