CODEWITHSUNDEEP
sqlgoogle-bigquerycommon-table-expression
cshin9
cshin9

Reputation: 1490

WITH in BigQuery

Does BigQuery support the WITH clause? I don't like formatting too many subqueries.

For example:

WITH alias_1 AS (SELECT foo1 c FROM bar)
, alias_2 AS (SELECT foo2 c FROM bar a, alias_1 b WHERE b.c = a.c)
SELECT * FROM alias_2 a;

Upvotes: 24

Views: 93845

Answers (3)

trillion
trillion

Reputation: 1401


   query 1 = select name, country from table_1

   query 2 = select country, city, address, phone from table_2


with countries  as (

select * from table_1
where name is not null

)

select 
  table_1.*,
  table_2.* except(country) -- except will not fetch the column country toavoid the same column twice in the output, unless you want it to
from table_1
left join table_2
on table_1.country = table_2.country

Upvotes: 0

HKE
HKE

Reputation: 473

BigQery Standard SQL is supporting WITH clause. The syntax is as shown below

with table2 as (Select column1,column2 from table1)
    select column1 from table2

Upvotes: 12

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Recently introduced BigQuery Standard SQL does support WITH clause
See more about WITH clause

See also how to Enabling Standard SQL

Upvotes: 29

Related Questions