In this query I am trying to get email address of users that were "created" in month of June 2016 and have never existed before but this does not seem to work at all.
I need to filter out users emails that have existed before June 2016 and get only the new ones created in June 2016.
What seems wrong with this query?
select DISTINCT SF.customer_email, SF.created_at, historcialEmails.customeremail,
SFO.created_at as createdAt
DATE(SFO.created_at) <= '2016-05-31'
) AS historcialEmails
ON historcialEmails.customeremail = SF.customer_email
YEAR(DATE(SF.created_at)) = '2016'
MONTH(DATE(SF.created_at)) = '6'
SF.customer_email IS NULL
Too long and format dependent for comment but, incidentally, appropriately indexed, the following is optimised:
sf.created_at <= '2016-05-31 00:00:00' ... AND sf.created_at BETWEEN '2016-06-01 00:00:00' AND '2016-07-01 00:00:00' WHERE sf.customer_email IS NULL
To get back to the answer, you will notice that I've swapped the position of the WHERE clause. Except for IS NULL, conditions on OUTER JOINED tables (sf is the outer joined table in your example) belong on the JOIN itself. Note also that use of RIGHT JOIN is vanishingly rare. Normally a LEFT JOIN would be used, and the tables swapped around.