Create a left join with raw string data

I have a list of e-mails in a text file and a Person table with an email column.

I need to know which of the emails are in the table, and which are not.

So I was thinking in creating a query and do some kind of left join in my raw e-mail data with the Person table.

Now, I can do this:

select count(*) from PERSON p where p.EMAIL in ("email1@mail","[e-mail]");

But, what I want is to return something like this:

Raw_Email         email

[e-mail]   [e-mail]
[e-mail]     null
XXXXX             XXXXX

Answer

Loading the data from the text file into some table is the best option (the table may be temporary). Loaded data may then be used by the common way.

Another option is in using CSV Engine – but this engine must be available on your server instance (check SHOW ENGINES output). You must create table using this engine joined to your file then use this table by the common way.

Anycase you must have CREATE TABLE privilege.

The last option is in use LOAD_FILE() function. File content will be loaded as one string literal which may be parsed and splitted on separate values (this is easy enough on recent MySQL version). But you must to have secure_file_privilege option set, and if it is not empty then the file must be placed into specified folder. This option does not need in special additional privileges. Alternatively this function can be used in stored procedure where loaded data will be used in dynamic SQL.