Simran Kaur Simran Kaur - 1 year ago 88
SQL Question

right join in my sql and subquery

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,
sales_flat_order SF
select SFO.customer_email
AS customeremail,
SFO.created_at as createdAt
sales_flat_order SFO
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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download