Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

NOT IN operator in hive with a subquery

NOT IN operator in hive with a subquery

Expert Contributor

I have a subquery like this:

select SF.customer_email , SF.created_at
FROM 
Table1 SF
WHERE
YEAR(TO_DATE(SF.created_at)) = '2016'
AND 
MONTH(TO_DATE(SF.created_at)) = '6'
AND 
SF.customer_email 
NOT IN (
select SFO.customer_email FROM Table1 SFO 
WHERE
TO_DATE(SFO.created_at) < '2016-05-31'
)​

​I have checked manually and I should get results for the query but it returns empty result set.

Note: I am using same table in the sub-query as well. Just a different condition on date column.