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

Question

I have a list of e-mail addresses, some of them are in my table, some of them are not. I want to select all e-mails from that list and whether they are in the table or not.

I can get users whose mail adresses are in the table like this:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

But how can I select values in that list which are not exist in the table?

Moreover, how can I select like this:

E-Mail | Status
email1 | Exist  
email2 | Exist  
email3 | Not Exist  
email4 | Exist  

Thanks in advance.

Answer 1

For SQL Server 2008

SELECT email,
       CASE
         WHEN EXISTS(SELECT *
                     FROM   Users U
                     WHERE  E.email = U.email) THEN 'Exist'
         ELSE 'Not Exist'
       END AS [Status]
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  

For previous versions you can do something similar with a derived table UNION ALL-ing the constants.

/*The SELECT list is the same as previously*/
FROM (
SELECT 'email1' UNION ALL
SELECT 'email2' UNION ALL
SELECT 'email3' UNION ALL
SELECT 'email4'
)  E(email)

Or if you want just the non-existing ones (as implied by the title) rather than the exact resultset given in the question, you can simply do this

SELECT email
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  
EXCEPT
SELECT email
FROM Users