BigQuery SQL - WITH Clause

visibility 480 access_time 2 years ago languageEnglish timeline Stats
timeline Stats
Page index 1.01
more_horiz

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"
  }
]


copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext
Teradata RANDOM Number
visibility 522
thumb_up 0
access_time 2 years ago
Load JSON File into BigQuery
visibility 3,123
thumb_up 0
access_time 2 years ago
Load JSON File into BigQuery
Spark SQL - Convert String to Date
visibility 15,208
thumb_up 0
access_time 30 days ago