I have to find the values in one table that are not in another table column. This is AWS Athena (Presto is the query engine).
I did this which I believe should work:
select count(*) cnt
from all_users m left join login_activity a
on m.user_id = a.userid
where a.userid is null;
-- count is 1,424,527
I was expecting that this result would be the same
select count(*) cnt
from all_users
where user_id not in (select distinct userid from login_activity);
-- count is 1,423,447
My main question is: are either of these approaches better or more accurate than the other?
The count differs by around 1,000. Why would this be?