T-SQL: how to select values ​​in a list of values ​​that are NOT in a table?

Question

I have a list of email addresses, some of them are in my table, some of them are not. I want to select all emails from this list and be in the table or not.

I can get users whose email addresses are in the table as follows:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

But how can I select values ​​in this list that are not in the table?

Also, how can I choose like this:

 E-Mail | Status email1 | Exist email2 | Exist email3 | Not Exist email4 | Exist 

Thanks in advance.

Answer 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 UNION ALL -derived constant in 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) 

Answer 2

You need to somehow create a table with these values, and then use NOT IN . This can be done using a temporary table, CTE (Common Table Expression), or Table Values ​​Designer (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 the LEFT JOIN or 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) 

Answer 3

You should have a table with a list of letters to check. Then run 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 

Answer 4

If you do not want the letters in the list that are in the database, you 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 get a result like

 name | email | emailadres | existence -----|--------|------------|---------- NULL | NULL | [e-mail] | Not exists Jan | [e-mail] | [e-mail] | Exists 

Using the IN or EXISTS statements is harder, then the left join in this case.

Good luck :)

Answer 5

This should work with all versions of SQL.

 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 

Answer 6

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