Search code examples
sqljoininner-joingoogle-bigqueryreddit

How to use BigQuery's union all with an inner join?


I am attempting to join all comment tables (shards of comments each month) to a posts table. Is there a way for me to perform a union all before the inner join? Details on the union all operator can be found in the documentation here. My query with only 1 of the comments table is as follows:

SELECT c.score, c.body, c.link_id, c.parent_id, p.created_utc, c.created_utc
FROM [fh-bigquery:reddit_comments.2016_01] AS c
INNER JOIN [fh-bigquery:reddit_posts.full_corpus_201512] AS p
ON c.parent_id = p.name
WHERE SUBSTR(c.parent_id, 1, 2) = 't3'
ORDER BY c.score DESC
LIMIT 10

Solution

  • As Mikhail Berlyant pointed out in his answer, modifying the query as such accomplished what I needed.

    SELECT c.score, c.body, c.link_id, c.parent_id, p.created_utc, c.created_utc, (c.created_utc - p.created_utc) AS time_diff
    FROM (
      SELECT * 
      FROM 
        [fh-bigquery:reddit_comments.2015_11],
        [fh-bigquery:reddit_comments.2015_12],
        [fh-bigquery:reddit_comments.2016_01],
    ) AS c
    INNER JOIN [fh-bigquery:reddit_posts.full_corpus_201512] AS p
    ON c.parent_id = p.name
    WHERE SUBSTR(c.parent_id, 1, 2) = 't3'
    ORDER BY c.score DESC
    LIMIT 100