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

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

New Contributor

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

New Contributor

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

 

Thanks in Advance.