Select default value if no matched record

Question

Take a table of the following structure:

ID, Email, Name

I am given a list of emails, and need to find out: which emails are present in the table as well as which ones are not. For those present, return the corresponding ID for each email.

I hope to achieve this using as few SQL statements as possible.

Two problems:

1. Since I need to match each given email to an ID (if present), the order of the returned IDs must allow me to match them individually to given emails. If we do a simple SELECT ID FROM Table WHERE Email IN (...), the order of results may not match with the order of the list of emails given for the IN clause (at least for SQLite).

2. A normal SELECT ... WHERE ... IN () statement also does not directly tell you which email are not present. I've tried the following in SQLite, while it runs, it doesn't give me what I want: SELECT COALESCE(ID, -1) AS UID, Email FROM Table WHERE Email IN (...): it returns only the matched records.

A naive solution is to run as many SELECT as the number of given emails, one for each, so that you know for each email if a record is present and if so what the ID is. But this may cause performance problem if the number of emails is large, especially when taking into account client-server communication overhead for SQL servers.

I am interested in a solution for SQLite as well as the equivalent for larger SQL servers.

Answer 1

I think, you have to try this way. I am Given this answer in MSSQL perspective

Crete Temp Table, and store your email list in that.

create table #emails(
email varchar(50)
)

insert into #emails values ('b@g.com')
insert into #emails values ('c@g.com')
insert into #emails values ('d@g.com')
insert into #emails values ('e@g.com')
insert into #emails values ('f@g.com')

Suppose yiou have users table like this

create table users(
  id int,
  email varchar(50)
)

insert into users values (1, 'a@g.com')
insert into users values (2, 'b@g.com')
insert into users values (3, 'c@g.com')
insert into users values (4, 'd@g.com')

Then use join to fetch records

Select #emails.email, users.id,    
       CASE WHEN users.ID IS NULL THEN 'Not Present'
            ELSE 'Present' 
       END IsPresentInTable
From #emails left join users ON #emails.email = users.email

Then output will be

email   id      IsPresentInTable
b@g.com 2         Present
c@g.com 3         Present
d@g.com 4         Present
e@g.com (null)  Not Present
f@g.com (null)  Not Present

Sql Fiddle

Answer 2

To resolve second problem this trick with left join will work:

with input_emails(email) as (
values ('email1'),('email2'), ('email3')
)

select * from input_emails left join emails on input_emails.email = emails.email;

However it's not guaranteed that order of email will preserve (but it most cases it will be if you have index on email field and list of emails is not as big as table with emails). To ensure that order will be the same anyway trick with row_number will help:

with input_emails(email) as (
    values ('email1'),('email2'), ('email3')
),
input_emails_with_row_numbers as (
    select email, row_number() over () from input_emails
)

select * from input_emails_with_row_numbers left join emails
on input_emails_with_row_numbers.email = emails.email order by row_number;