T-SQL How to Select Values in Value List that are NOT IN the Table

Question

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

User · Answer

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)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     a b com      Not exists Jan    j j nl   j j nl       Exists   Using the IN or EXISTS operators are more heavy then the left join in this case   Good luck

[sql] T-SQL: How to Select Values in Value List that are NOT IN the Table?

Examples related to sql

Examples related to sql-server

Examples related to tsql