BigQuery : WITH clause behavior in multiple JOIN conditions

Question

For readability, I have defined "org_location_ext" clause in the query as follows.

This "org_location_ext" is first used to join with the main fact-table "LOCATION_SALES". It is used in other JOIN conditions as well.

According to the BigQuery documentation : https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

The WITH clause contains one or more named subqueries which execute every time a subsequent SELECT statement references them

I want to know the behavior for this case.

Does this query executes the "org_location_ext" WITH clause multiple times ?

Or when the SELECT query gets executed, a temporary table is created for "org_location_ext" and use this temporary table for all the JOINs.

Basically, after the first JOIN with the fact-table , later joins use that "filtered" result for their joins , or do they rerun the WITH clause ?

WITH org_location_ext AS (
  SELECT *
  FROM ORG_LOC_MASTER AS loc_master
  JOIN LOC_REGN1 as regn1 ON loc_master.id = regn1.id
  JOIN ...
  JOIN ...
)

SELECT 
    ..
    org_location_ext.store_class,
    org_location_ext.country,
    org_location_ext.
    ..
    ..
FROM LOCATION_SALES AS sales
  JOIN org_location_ext ON org_location_ext.area_id = sales.area_id AND org_location_ext.date = sales.date

  JOIN ....
  JOIN ....
  JOIN COUNTRY_VAT AS vat ON vat.key1 =TBL_Y.key1 AND vat.country_code = org_location_ext.country_code

How this case different from the explanation you referenced? — Mikhail Berlyant
the JOINs refers the "org_location_ext" multiple times. I assumed when this query gets executed, a temporary table is created for "org_location_ext" and use this temporary table for subsequent JOINs. — Ashika Umanga Umagiliya
I think the question the OP really has here is whether the CTE just ends up as SQL code inlined in the actual query, or does the CTE get evaluated as a temporary table. — Tim Biegeleisen
But that is exactly opposite to what documentation says!? From how I read doc - no temp table is created in such cases — Mikhail Berlyant
basically, after the first JOIN with the fact-table , later joins use that "filtered" result , or do they rerun the WITH clause ? — Ashika Umanga Umagiliya

Sergey Geron Sergey Geron · Accepted Answer · 2020-10-23T06:22:26

It depends on the query plan. Consider checking a query plan. You'll see how many times any specific table is accessed.

BigQuery : WITH clause behavior in multiple JOIN conditions

1 Answers