T-SQL: How to Select Values in Value List that are NOT IN the Table?
Solution 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
Solution 2
You need to somehow create a table with these values and then use NOT IN
. This can be done with a temporary table, a CTE (Common Table Expression) or a Table Values Constructor (available in SQL-Server 2008):
SELECT email
FROM
( VALUES
('email1')
, ('email2')
, ('email3')
) AS Checking (email)
WHERE email NOT IN
( SELECT email
FROM Users
)
The second result can be found with a LEFT JOIN
or an EXISTS
subquery:
SELECT email
, CASE WHEN EXISTS ( SELECT *
FROM Users u
WHERE u.email = Checking.email
)
THEN 'Exists'
ELSE 'Not exists'
END AS status
FROM
( VALUES
('email1')
, ('email2')
, ('email3')
) AS Checking (email)
Solution 3
You should have a table with the list of emails to check. Then do this query:
SELECT E.Email, CASE WHEN U.Email IS NULL THEN 'Not Exists' ELSE 'Exists' END Status
FROM EmailsToCheck E
LEFT JOIN (SELECT DISTINCT Email FROM Users) U
ON E.Email = U.Email
Solution 4
When you do not want to have the emails in the list that are in the database you'll can do the following:
select u.name
, u.EMAIL
, a.emailadres
, case when a.emailadres is null then 'Not exists'
else 'Exists'
end as 'Existence'
from users u
left join ( select 'email1' as emailadres
union all select 'email2'
union all select 'email3') a
on a.emailadres = u.EMAIL)
this way you'll get a result like
name | email | emailadres | existence
-----|--------|------------|----------
NULL | NULL | [email protected] | Not exists
Jan | [email protected] | [email protected] | Exists
Using the IN or EXISTS operators are more heavy then the left join in this case.
Good luck :)
Comments
-
kubilay about 2 years
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.
-
Admin over 12 yearsThat gives the values in the table that are not in the list. The question asks it the other way around: the ones in the list that are not in the table.
-
Admin over 12 yearsWith the edit, it still won't be right if
Users.email
is nullable (and we don't know if it is) -
kubilay over 12 yearsThanks, I didn't want to go with long way. Martin's answer did it.
-
kubilay over 12 yearsThank you, your answer is late but correct, too. I'm glad I didn't have to create a table for values.
-
ypercubeᵀᴹ over 12 years@hvd: You are right, we don't know if it is nullable. The query will answer correctly though.
-
Martin Smith over 12 years
EXISTS
will be more efficient than outer join as it can short circuit. Yours can bring back the same address multiple times if there are duplicates inUsers
. See Subqueries in CASE Expressions for some details as to howEXISTS
is handled. -
Admin over 12 yearsYour second query will, your first won't.
WHERE 'a' IN ('b', NULL, 'c')
isUNKNOWN
rather thanFALSE
, andNOT UNKNOWN
is stillUNKNOWN
. In other words if any user's email address isNULL
, the first query will not return anything. -
ypercubeᵀᴹ over 12 years@hvd: I know but that could be the "correct" behaviour for the OP :) Why else does he have Nulls in the column after all?
-
Admin over 12 yearsYes, technically, there's a chance that when a user's email address is unknown, that user's happens to be one of the few email addresses in the OP's hardcoded list, and the OP wants to account for the possibility that it is one of those few email addresses. I approximate that chance low enough to discard that possibility.