title: SQL getting items not in a list
body: <p>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:</p>
  
  <pre><code>select *
  from UserTbl
  where username in ('email1','email2','email3',...'email821')
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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. </p>
  
  <p>If anyone could help me out on this it would be greatly appreciated.</p>
  
  <p>Thanks,
  Ben</p>