CODEWITHSUNDEEP
google-bigquery
James Bond
James Bond

Reputation: 19

BigQuery NOT IN is not working with subquery

In BigQuery I have subquery like this:

SELECT country, city, count(cases) as total_cases 
FROM Table1 
where country = 'US'
AND country || city NOT IN (SELECT distinct country || city 
FROM Tablex)
GROUP BY 1, 2;

But I see given below error:

"Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN."

I tried with

WITH Alias_table1 AS (Query 1),

Alias_table2 AS (Query 2),

query as (Select * from Alias_Table1
LEFT JOIN Alias_Tabl2
ON Alias_Table1.ID = Alias_Table2.ID
WHERE Alias_Table2 IS NULL)

SELECT * FROM query

Upvotes: 0

Views: 1094

Answers (1)

Jiho Choi
Jiho Choi

Reputation: 1311

If you want to filter out Query 1 with Query 2, you can try ANTI LEFT JOIN (the possible use case would be query 1 has users and query 2 has whitelists).

WITH
alias_table1 AS (
    -- Query 1
),
alias_table2 AS (
    -- Query 2
),
query as (
    Select *
    FROM alias_Table1
    LEFT JOIN alias_Tabl2 USING (ID)
    WHERE alias_Table2.ID IS NULL  -- <- ****
    -- WHERE alias_Table2 IS NULL
)
SELECT *
FROM query
;

enter image description here

Upvotes: 0

Related Questions