CODEWITHSUNDEEP
sqlgoogle-bigquery
Paul Bendevis
Paul Bendevis

Reputation: 2621

BigQuery - reference results from subquery in another part of the same query

I often run into the following problem in my queries where I build a large complicated subquery which I want to reference multiples times in the same query (but maybe filter it slightly differently):

Select * FROM (BIG SUB QUERY WHERE field='one') as a
INNER JOIN 
(SAME BIG SUB QUERY WHERE field = 'two') as b
ON a.id = b.id

I want to avoid using temporary tables because I'm using BigQuery and want to execute just a single query (for the sake of speed). Is there a way to keep subqueries around and reference them elsewhere? If I copy and paste the BIG SUB QUERY text, will Big Query be optimized to run the near identical SUB QUERIES efficiently?

Upvotes: 0

Views: 4475

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

You should use BigQuery Standard SQL 

WITH BIG_SUB_QUERY AS (
  YOUR 
  BIG SUB QUERY 
  HERE
)
SELECT * FROM BIG_SUB_QUERY AS a
INNER JOIN BIG_SUB_QUERY AS b
ON a.id = b.id AND a.field='one' AND b.field='two'

See more details for WITH Clause

Upvotes: 6

Related Questions