access_time 5 months ago languageEnglish
more_vert

BigQuery SQL - WITH Clause

visibility 76 comment 0

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

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 1599
thumb_up 0
access_time 12 months ago
visibility 206
thumb_up 0
access_time 5 months ago
visibility 172
thumb_up 0
access_time 3 months ago