question NOT IN operator in hive with a subquery in Archives of Support Questions (Read Only)

NOT IN operator in hive with a subquery

simran_k — Thu, 07 Jul 2016 14:22:43 GMT

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 resultset.

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

Re: NOT IN operator in hive with a subquery

bpreachuk — Thu, 07 Jul 2016 20:39:06 GMT

Hi @Simran Kaur. This query 'should' work. 😉 Quick things to double-check...

1. These 2 queries return counts > 0

select count(*) 
FROM Table1 SF 
WHERE YEAR(TO_DATE(SF.created_at)) = '2016'
AND   MONTH(TO_DATE(SF.created_at)) = '6'

select count(*) FROM Table1 SFO 
WHERE TO_DATE(SFO.created_at) < '2016-05-31'

2. based on the counts returned - is it possible that there are no customers from June 2016 that did not receive an email prior to 2016 June? (i.e. the correct result is in fact zero)?