Using Google BigQuery's Comma as UNION ALL with IN clause

Question

I am attempting to perform the following query:

SELECT 
  author, link_id, COUNT(link_id) as cnt
FROM 
  [fh-bigquery:reddit_comments.2015_12],
  [fh-bigquery:reddit_comments.2015_11]
WHERE link_id IN (
  SELECT posts.name
  FROM [fh-bigquery:reddit_posts.full_corpus_201512] AS posts
  WHERE posts.subreddit = 'politics'
  ORDER BY posts.created_utc DESC
  LIMIT 300
)
GROUP BY author, link_id
ORDER BY author

I receive this error message upon execution: JOIN (including semi-join) and UNION ALL (comma, date range) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

Removing one of the comments tables works fine however I can't seem to figure out how BigQuery's Comma as UNION ALL works. I've attempted to move the union to an inner query but I still get the same error.

Kevin Eger Kevin Eger · Accepted Answer · 2016-02-13T21:52:45

The error was in my misunderstanding of move the UNION ALL to an inner query. The resolve the error, I had to put the two tables in a basic select * from .... The working query is as follows:

SELECT 
  author, link_id, COUNT(link_id) as cnt
FROM (
  SELECT * 
  FROM
    [fh-bigquery:reddit_comments.2015_12],
    [fh-bigquery:reddit_comments.2015_11]
  )
WHERE link_id IN (
  SELECT posts.name
  FROM [fh-bigquery:reddit_posts.full_corpus_201512] AS posts
  WHERE posts.subreddit = 'politics'
  ORDER BY posts.created_utc DESC
  LIMIT 300
)
GROUP BY author, link_id
ORDER BY author

Using Google BigQuery's Comma as UNION ALL with IN clause

1 Answers