How to replace subquery with in statement in bigquery

user_382920 0
Viewed 199

I happen to stumble with a problem using bigquery, I have to build a query where I need to limit the number of ids within the left join to a subset of a query, unfortunately bigquery does not support subquery.

I've been trying to find a solution that will allow me to place this constraint within the join but haven't been successful usually the solution I encounter suggest the usage of crossjoin but I haven't had success with it so far, here is in a nutshell the table structure I have and the query I'm trying to construct:

#standardSQL
WITH User AS (
  SELECT 1 AS id, "A" AS items UNION ALL 
  SELECT 2 AS id, "B" AS items UNION ALL 
  SELECT 3 AS id, "c" AS items),
Label_User AS (
  SELECT 1 AS user_id, 1 AS label_id UNION ALL 
  SELECT 1 AS user_id, 4 AS label_id UNION ALL 
  SELECT 1 AS user_id, 3 AS label_id UNION ALL 
  SELECT 2 AS user_id, 1 AS label_id UNION ALL 
  SELECT 2 AS user_id, 2 AS label_id),
Labels AS (
  SELECT 1 AS id, "Test" AS label UNION ALL 
  SELECT 2 AS id, "Admin" AS label UNION ALL
  SELECT 3 AS id, "Local" AS label UNION ALL
  SELECT 4 AS id, "External" AS label) 

select * from User left join Label_User on id=user_id and 
          label_id in (select id from Labels where label = "External" or label ="Local")

-- This works for a single record of label id
-- select * from User left join Label_User on id=user_id and label_id = 1

Any help would be very appreciated.

Edit 1

Thanks @mikhail-berlyant for his suggestion, but the issue I've found with having the condition in the where clause, it's that it filters out some records that I need, so the result I'm looking for looks like this:

id  items   user_id label_id    
1       A     1       4 
1       A     1       3
2       B     null    null
3       C     null    null

But having the filter in the where output this:

Row id  items   user_id label_id    
1        A          1       4   
1        A          1       3
google-bigquery
1 Answers

Below is for BigQuery Standard SQL

#standardSQL
SELECT *
FROM User 
LEFT JOIN (
  SELECT * 
  FROM User 
  LEFT JOIN Label_User 
  ON id = user_id 
  WHERE label_id IN (SELECT id FROM Labels WHERE label = "External" OR label ="Local")
)
USING (id, items)

when applied t sample data from your question - output as below

Row id  items   user_id label_id     
1   1   A       1       4    
2   1   A       1       3    
3   2   B       null    null     
4   3   C       null    null
user_52219440
Related