Raymond Raymond

BigQuery SQL - WITH Clause

event 2021-03-13 visibility 1,036 comment 0 insights toc
more_vert
insights Stats
toc Table of contents

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


More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts