hive query to check for entries added in a particular month that do not exist already in the database
<p>I need to check for new emailId's added in 06-2016 that have not existed in database ever before that.</p>

<p>I wrote the query using NOT IN operator:</p>

<pre><code>select DISTINCT SF.customer_email
FROM 
Magento.sales_flat_order 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 Magento.Sales_flat_order SFO 
WHERE
TO_DATE(SFO.created_at) &lt;= '2016-05-31'
)
</code></pre>

<p>I have verified that there are new emaiid's in the time but it returns empty result-set. Why is that? Infact, When I replace NOT IN operator with IN operator, it does return me the common ones but somehow NOT IN is behaving erratically.</p>