SQL getting items not in a list

Question

I am using MS SQL Server 2008 R2 and I have a list of emails from a spreadsheet that I inserted into a query against a user table. Out of 821 emails in the list, it returned 759 rows. Is there any easy way to get it to return those emails not in the table? I only have read access to the database so cannot create a table with the email list - only get results. Here is a simplified version of the query I used to get the list of those emails that were there:

select *
from UserTbl
where username in ('email1','email2','email3',...'email821')

I could come up with a Unix shell solution, but it would be far more useful to know how to do it in MS SQL. I actually found something close to a solution from stackoverflow ("T-SQL: How to Select Values in Value List that are NOT IN the Table?") , but it did not seem to work for me (for my needs I would only want a list of those not in the table output):

SELECT username,
    CASE
        WHEN EXISTS(SELECT * FROM UsersTbl tu WHERE E.email = tu.username) THEN 'Exist'
        ELSE 'Not Exist'
    END AS
FROM (VALUES('email1'),('email2'),('email3'),('email4')) E(email)

This gave me the error "Incorrect syntax near the keyword 'FROM'" when I run the query. As an aside, I was googling looking around for a description of the VALUES keyword used above and didn't find anything helpful on it.

If anyone could help me out on this it would be greatly appreciated.

Thanks, Ben

Answer 1

If you just want the list of e-mails that aren't already present, this seems much simpler:

SELECT e.email
FROM 
(
  VALUES('email1'),('email2'),('email3'),('email4')
) AS e(email)
WHERE 
NOT EXISTS
(
  SELECT 1
    FROM dbo.UsersTbl 
    WHERE username = e.email
);

Or even simpler:

SELECT e.email 
FROM 
(
  VALUES('email1'),('email2'),('email3'),('email4')
) AS e(email)
EXCEPT
SELECT username FROM dbo.UsersTbl;

To see why I prefer NOT EXISTS / EXCEPT over LEFT JOIN or other alternatives, see:

http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join