WITH Clause - Common table expressions in Bigquery

You can easily create CTEs (temp tables that live for the session duration) in Bigquery by using query names. The query names in a WITH clause (see WITH Clause) act like names of temporary tables that you can reference anywhere in the FROM clause. In the example below, subQ1 and subQ2 are with_query_names

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)

SELECT DISTINCT * FROM subQ2;

NOTE: The WITH clause hides any permanent tables with the same name for the duration of the query, unless you qualify the table name, e.g. dataset.Roster or project.dataset.Roster

nVector

posted on 10 May 20