MillerMedia MillerMedia - 5 months ago 11
SQL Question

MySQL SELECT EXISTS on Multiple Values

I believe this should be relatively simple but I can't seem to figure out the logic to it.

I'll start with what I'm looking for. I wanted to be able to pass in values (such as e-mail addresses) through a SELECT query and have it output whether those rows exist or not. It would be something like this:

# PASS Values test@test.com and example@example.com in.
# For these purposes let's pretend that test@test.com exists
# and example@example.com does not.

+----------------------+--------+
| E-mail | Exists |
+----------------------+--------+
| test@test.com | 1 |
| example@example.com | 0 |
+----------------------+--------+


There is the answer here for a single result: MySQL EXISTS return 1 or 0 but I would like to expand on that and am having issues figuring it out.

Any help would be great. Thanks!

Answer

Assuming I'm understanding your question correctly, you can't do this with in alone. One option is to create a subquery with an outer join, and then use case:

select t.email, 
       case when yt.email is not null then 1 else 0 end `exists`
from (select 'test@test.com' as email union all select 'example@example.com') t
    left join yourtable yt on t.email = yt.email

Note: backticks are needed on exists in the statement.