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.
This question is related to
sql
sql-server
tsql
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 :)
This Should work with all SQL versions.
SELECT E.AccessCode ,
CASE WHEN C.AccessCode IS NOT NULL THEN 'Exist'
ELSE 'Not Exist'
END AS [Status]
FROM ( SELECT '60552' AS AccessCode
UNION ALL
SELECT '80630'
UNION ALL
SELECT '1611'
UNION ALL
SELECT '0000'
) AS E
LEFT OUTER JOIN dbo.Credentials C ON E.AccessCode = c.AccessCode
Use this : -- SQL Server 2008 or later
SELECT U.*
FROM USERS AS U
Inner Join (
SELECT
EMail, [Status]
FROM
(
Values
('email1', 'Exist'),
('email2', 'Exist'),
('email3', 'Not Exist'),
('email4', 'Exist')
)AS TempTableName (EMail, [Status])
Where TempTableName.EMail IN ('email1','email2','email3')
) As TMP ON U.EMail = TMP.EMail
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)
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
Source: Stackoverflow.com