Temporary CTE Table in Bigquery using WITH Clause

The WITH clause is useful primarily for readability, because BigQuery does not materialize the result of the queries inside the WITH clause. If a query appears in more than one WITH clause, it executes in each clause.

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;

BigQuery supports WITH clauses in subqueries, such as table subqueries, expression subqueries

nVector

posted on 20 Sep 19