How to check and pull data of multiple emails from an account table?

Question

I have an array of about 1,000 emails like ['a@a.com', 'b@b.com', ..., 'zz@zz.com'] that I need to check. If an email exists in the account table, I need to pull the first and last name of the row that matches the email in the account table.

The account table looks like this:

CREATE TABLE account (
   id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
   email VARCHAR(255) NOT NULL,
   first_name VARCHAR(255),
   last_name VARCHAR(255)
);

What is the best way to achieve this without looping through the array?

Does this answer your question? [Passing an array to a query using a WHERE clause](https://stackoverflow.com/questions/907806/passing-an-array-to-a-query-using-a-where-clause) — kmoser, Oct 10 '21 at 02:41

score 0 · Answer 1 · answered Oct 10 '21 at 05:03

First, extract all values that matches the emails using IN() syntax.

Second, GROUP the values by email to remove duplicates.

Third, get only the lowest value (the first) via aggregation MIN().

Fourth, self JOIN with filtered id

SELECT * FROM account t1
JOIN (
    SELECT MIN(id) FROM account
    WHERE email IN ('a@a.com', 'b@b.com', ...)
    GROUP BY email
) t2 ON t2.id = t1.id

How to check and pull data of multiple emails from an account table?

1 Answers1