CODEWITHSUNDEEP
google-bigquery
Fabian Bosler
Fabian Bosler

Reputation: 2510

BigQuery - WITH Statement - reference earlier subquery in WHERE condition in later subquery

Typically a WITH Statement has the following form

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;

Now I was wondering if it is possible to do something like this:

WITH
  relevant_ids AS (SELECT ids FROM table WHERE condition),
  actual_data AS (SELECT stuff FROM large_table WHERE id in relevant_ids)

I know I could use the first query and put it directly in the where condition of the second query, but I feel like this isn't very easy to read.

Upvotes: 0

Views: 423

Answers (1)

rtenha
rtenha

Reputation: 3616

To specifically address your second query...

WITH
  relevant_ids AS (SELECT id FROM table WHERE condition),
  actual_data AS (SELECT stuff FROM large_table WHERE id in (select id from relevant_ids))
SELECT * from actual_data

The code inside CTEs must still be valid SQL.

However, consider just using a join.

WITH
  relevant_ids AS (SELECT id FROM table WHERE condition)
SELECT 
  large_table.stuff 
FROM large_table 
INNER JOIN relevant_ids USING(id)

Upvotes: 3

Related Questions