sql server - T-SQL: How to Select Values in Value List that are NOT IN the Table? -

sql server - T-SQL: How to Select Values in Value List that are NOT IN the Table? -

i have list of e-mail addresses, of them in table, of them not. want select e-mails list , whether in table or not.

i can users mail service adresses in table this: select u.* users u u.email in ('email1', 'email2', 'email3')

but how can select values in list not exist in table?

moreover, how can select this:

e-mail | status email1 | exist email2 | exist email3 | not exist email4 | exist

thanks in advance.

for sql server 2008

select email, case when exists(select * users u e.email = u.email) 'exist' else 'not exist' end [status] (values('email1'), ('email2'), ('email3'), ('email4')) e(email)

for previous versions can similar derived table union all-ing constants.

/*the select list same previously*/ ( select 'email1' union select 'email2' union select 'email3' union select 'email4' ) e(email)

sql sql-server tsql