Simran Kaur Simran Kaur - 4 months ago 14
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,
historcialEmails.createdAt
FROM
sales_flat_order SF
RIGHT JOIN
(
select SFO.customer_email
AS customeremail,
SFO.created_at as createdAt
FROM
sales_flat_order SFO
WHERE
DATE(SFO.created_at) <= '2016-05-31'
) AS historcialEmails
ON historcialEmails.customeremail = SF.customer_email
WHERE
YEAR(DATE(SF.created_at)) = '2016'
AND
MONTH(DATE(SF.created_at)) = '6'
AND
SF.customer_email IS NULL

Answer

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.

Comments