CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Anderson
Simon Anderson

Reputation: 29

BigQuery SQL Exclusion NOT IN empty results

I'm having problems with this not returning any values. There are accounts in the database that match this criteria. Somewhat confused why they aren't being returned. Any suggestions?

select accountid from `table1` 
where not in (select accountid from `table1` where action != "Action8")

Upvotes: 1

Views: 840

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Do not use not in. Semantically, it is counter-intuitive. If any values in the subquery are NULL, then no rows are returned.

Use not exists instead;

select t1.accountid
from `table1` t1
where not exists (select 1
                  from table1 tt1
                  where tt1.accountid  = t1.accountid and
                        tt1.action <> 'Action8'
                 );

Or use group by and having:

select t1.accountid
from table1 t1
group by t1.accountid
having sum(case when action = 'Action8' then 1 else 0 end) = 0;

Upvotes: 3

esqew
esqew

Reputation: 44698

Ensure you're referencing the field you don't want to be present in the subquery result:

select accountid from `table1` 
where accountid not in (select accountid from `table1` where action != "Action8")

Upvotes: 0

Related Questions