Take One Table As Input And Output Using Another Table Bigquery

I have one table and want to use it as my input for a query pulling from another table: input table: +----------+--------+ | item | period | +----------+--------+

Solution 1:

These are all equivalent:

1.

SELECT word, corpus FROM [publicdata:samples.shakespeare] 
WHERE (word OR corpus) IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))

2.

SELECT word, corpus FROM [publicdata:samples.shakespeare] 
WHERE word IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
OR corpus IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))

3.

SELECT word, corpus
FROM (
  SELECT word, corpus FROM [publicdata:samples.shakespeare] 
  WHERE word IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
), (
  SELECT word, corpus FROM [publicdata:samples.shakespeare] 
  WHERE corpus IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
)

The 3rd one works in BigQuery - but might produce some duplicates.

To prevent duplicates (1 row in this case):

SELECT word, corpus
FROM (
  SELECT word, corpus FROM [publicdata:samples.shakespeare] 
  WHERE word IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
), (
  SELECT word, corpus FROM [publicdata:samples.shakespeare] 
  WHERE corpus IN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
  AND word NOTIN (SELECT x FROM (SELECT'hamlet' x), (SELECT'about' x))
)

(replace (SELECT 'hamlet' x), (SELECT 'about' x) with your favorite table)

Solution 2:

The problem seems to be a SQL problem, not a bigquery one. As Turophile pointed out in his comment, the correct syntax should be

WHERE (planning_category IN (SELECT item FROM input) OR item_name IN (SELECT item FROM input) )

Solution 3:

I believe Google is not letting us use IN in ORs, only in ANDs. That's really awful, I'm facing the same problem.

The solution

WHERE (planning_category IN (SELECT item FROM input) OR item_name IN (SELECT item FROM input) )

will not solve the problem, and you'll get the same error Semijoin expression (i.e. "x IN (SELECT ...)") must be a part of logical AND.