BigQuery SQL - WITH Clause

BigQuery provides WITH clause that can be used to define more than one subqueries that can be referenced in SELECT statement. The subquery will be executed each time when it is referenced.

Code snippet

The following code snippet provides an example of using WITH clause.

WITH
  SRC1 AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<int64>[1,2,3,4,5]) AS ID),
  SRC2 AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<int64>[4,5,6,7]) AS ID)
SELECT
  DISTINCT *
FROM
  SRC1 EXCEPT DISTINCT
SELECT
  *
FROM
  SRC2;

Output (in JSON format):

[
  {
    "ID": "1"
  },
  {
    "ID": "2"
  },
  {
    "ID": "3"
  }
]