Join and window pipeline stages

Real questions often need columns from two tables, or a comparison against a total. Both fit into a pipeline without nested queries.

How it works

A join brings matching rows and columns from two inputs together. A window expression then looks at a set of the joined rows, and EXTEND adds its result without changing the number of rows.

Common mistake

If a key repeats, the join produces duplicate rows. A window function such as AVG then counts every duplicate, so the duplicates weight the result.

Worked example

(SELECT 7 AS id, 'Ada' AS name UNION ALL SELECT 8, 'Lin')
|> INNER JOIN (SELECT 7 AS id, 40 AS score UNION ALL SELECT 8, 60) USING (id)
|> EXTEND AVG(score) OVER () AS average_score
|> ORDER BY id

USING matches the two id values and keeps a single id column next to name and score.

AVG looks at both joined rows, so average_score is 50 on each output row.

All BigQuery lessons