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

Solution 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

Solution 2

seems that you're just missing an alias after the "AS"

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

Solution 3

What you had was very close, you just needed to actually specify the alias for the second column. Also, I think you want to select E.email, not the null username column (if you were to join back on the table)

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

Although, to get the list of emails that were not imported, I would probably just do:

SELECT E.email
FROM (VALUES('email1'),('email2'),('email3'),('email4')) E(email)
LEFT OUTER JOIN UsersTbl tu ON E.email = tu.username
WHERE tu.username IS NULL

Or,

SELECT E.email
FROM (VALUES('email1'),('email2'),('email3'),('email4')) E(email)
WHERE NOT EXISTS
(
    SELECT 1
    FROM UsersTbl tu 
    WHERE E.email = tu.username
)

Comments

[Ban Atman]

On both versions I get the error "Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'VALUES'." Does VALUES allow the the list to be treated like a table?

[Adir D]

@BanAtman Your question was tagged as SQL Server 2008. Are you sure you're on SQL Server 2008 (or better)?

[Ban Atman]

Microsoft SQL Server Management Studio 10.50.2500.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 8.0.7601.17514 Microsoft .NET Framework 2.0.50727.5466 Operating System 6.1.7601

[lc.]

@BanAtman Does select * from (values('foo')) bar(baz) work?

[Lamak]

@BanAtman Are you sure you are looking at the properties of the server instead of your computer?.

[Ban Atman]

I get "Incorrect syntax near the keyword 'values'" for your simplified example as well (select * from (values('foo')....). The properties I listed were from the Help/About window where I click on the copy info. button.

[lc.]

@BanAtman Well I can verify the syntax works on both SQLFiddle and my instance of SQL Server 2012. I wonder if the server you're connecting to is truly 2008... If you can't use the VALUES() derived table syntax you can create a temp table, insert all the values you expect and use that instead.

[Ban Atman]

I tried this but don't have permissions to access a file like that. I did modify your example though by writing a quick Unix routine to output all the INSERT into @UserTbl... lines (822 of them) and pasted it into MS SQL Server: Declare @UserTbl Table (email nVarchar(255));INSERT into @UserTbl VALUES('.......... etc. select * from @UserTbl where email not in (select username from TblUser) and it worked. If TblUser also had a field called email, how would I access the one from @TblUser as doing @TblUser.email didn't work?

[HucKQC]

If you have a common email field what you can do is select * from @UserTbl ut join @TblUser tu On tu.email = ut.email and tu.username is null