Support Questions
Find answers, ask questions, and share your expertise

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.

2 REPLIES 2

Re: NOT IN operator in hive with a subquery

New Contributor

Check if subquery contain NULL value or not. If any of row have NULL value then NOT IN gives empty result.

Re: NOT IN operator in hive with a subquery

New Contributor

Hi, Can you explain why this happens and what is the workaround for this?

 

Thanks in Advance.