# 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. Source: https://docs.querylab.io/bigquery/ctes/ Updated: 2026-11-05 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 query** ```sql 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 (2 rows): | customer | spent | | --- | --- | | cy | 80 | | ana | 55 | `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** ```sql WITH all_words AS ( SELECT * FROM `bigquery-public-data.samples.shakespeare` ) SELECT 'all_words is never read' AS answer ``` BigQuery dry run: this query processes 0 B. Reading a CTE twice doesn't double the estimate. Both of these queries process 1,317,248 bytes: **one reference** ```sql 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 processes 1.3 MiB. **two references, same bytes** ```sql 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 processes 1.3 MiB. 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](https://docs.querylab.io/bigquery/temporary-tables/#temp-table-or-cte) 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** ```sql 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 (1 row): | same_value | | --- | | false | 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** ```sql 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 processes 5.5 GiB. **date filter inside the CTE** ```sql 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 processes 5.5 GiB. 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](https://docs.querylab.io/writing-sql/cost-limits/) 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 later** ```sql WITH totals AS (SELECT SUM(amount) AS total FROM orders), orders AS (SELECT 10 AS amount UNION ALL SELECT 20) SELECT total FROM totals ``` BigQuery rejects this query: ```text 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** ```sql 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 (1 row): | total | | --- | | 30 | Each name can appear once per `WITH` clause: **the same name twice** ```sql WITH orders AS (SELECT 1 AS id), orders AS (SELECT 2 AS id) SELECT id FROM orders ``` BigQuery rejects this query: ```text 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 one** ```sql 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 (1 row): | in_subquery | at_top_level | | --- | --- | | inner | outer | 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 statement** ```sql WITH orders AS (SELECT 1 AS id) SELECT id FROM orders; SELECT id FROM orders ``` BigQuery rejects this query: ```text 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 name** ```sql WITH totals (customer, spent) AS (SELECT 'ana', 55) SELECT customer, spent FROM totals ``` BigQuery rejects this query: ```text 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 keyword** ```sql WITH totals AS MATERIALIZED (SELECT 'ana' AS customer, 55 AS spent) SELECT customer, spent FROM totals ``` BigQuery rejects this query: ```text Syntax error: Expected "(" but got keyword MATERIALIZED at [1:16] ``` And `WITH` can't sit in front of `INSERT`: **WITH before INSERT** ```sql 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 ``` BigQuery rejects this query: ```text 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 INSERT** ```sql 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 (1 row): | id | | --- | | 1 | ## CTE, view or temp table | You need | Use | | ----------------------------------------------- | -------------------------------------------------- | | Named steps in one statement | CTE | | A result read by several statements in a script | [Temp table](https://docs.querylab.io/bigquery/temporary-tables/) | | Expensive work reused inside one statement | Temp table, so it runs once | | The same logic in many queries | [View](https://docs.querylab.io/bigquery/views/) | | A precomputed result that stays fresh | [Materialized view](https://docs.querylab.io/bigquery/materialized-views/) | | Hierarchies of unknown depth | [Recursive CTE](https://docs.querylab.io/bigquery/recursive-ctes/) | Recursive CTEs are the exception to the storage rule: BigQuery materializes their results. [Pipe syntax](https://docs.querylab.io/bigquery/pipe-syntax/) is another way to write a query as steps, and it can follow a `WITH` clause. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Query syntax, WITH clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause) - [Work with recursive CTEs](https://cloud.google.com/bigquery/docs/recursive-ctes)