MySQL syntax for treating an inline CSV as a temporary table?

Question

Temporary table may be overkill for what I'm trying to do. Basically, I want to invert the following MySQL query:

select email
from email_newsletter_subscriptions
where unsubscribeDate is not null
    and email in (
        # CSV list of email address
    )

This query gives me the list of email addresses that exist in the table and have unsubscribed. What I want are the list of emails in my in my CSV that don't exist in email_newsletter_subscriptions OR does exist but has a null unsubscribeDate.

I found one crazy work around that's surprisingly snappy for my dataset (~1k rows), but gets unwieldy just to create for larger input sets.

select email from (select 'john.doe@exmple.com' as email union all
                   select 'jane.do@example.com' as email) e
left join email_newsletter_subscriptions ens using(email)
where ens.email is null OR ens.unsubscribeDate is null;

Is there an easier syntax that can just take the input as a CSV without having to turn every line into a select ... union all monstrosity?

I'm also aware that we could load these into a temporary table and get a lot more options, but I'd really like to have a simple, single-query method for copy-paste drop in that works (I don't always have temporary table permissions on the target environment).

Props to http://stackoverflow.com/a/16359654/1146608 for the idea on column transposition. — Patrick M, Jul 13 '15 at 21:11

score 0 · Answer 1 · answered Jul 14 '15 at 04:34

A temporary table is the correct solution here. You can use LOAD DATA LOCAL to upload it from the local client, and then run a query similar to what you already posted.

CREATE TEMPORARY TABLE local_list (email VARCHAR(255) PRIMARY KEY );

LOAD DATA LOCAL INFILE '/tmp/input.csv' INTO TABLE local_list;

SELECT email
FROM local_list
LEFT JOIN email_newsletter_subscriptions ens
  USING (email)
WHERE 
  (ens.email IS NULL)
  OR
  (ens.unsubscribeDate IS NOT NULL);

MySQL syntax for treating an inline CSV as a temporary table?

1 Answers1