BigQuery SQL - WITH Clause

visibility 428 access_time 2 years ago languageEnglish

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.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 0.99
More from Kontext
Create Partitioned Table - Hive SQL
visibility 3,031
thumb_up 0
access_time 2 years ago
BigQuery SQL - UNION ALL
visibility 352
thumb_up 0
access_time 2 years ago
Spark - Read from BigQuery Table
visibility 1,607
thumb_up 0
access_time 2 years ago
Spark - Read from BigQuery Table