T-SQL: Wie wählt man Werte in der Werteliste aus, die NICHT in der Tabelle sind?

Question

Ich habe eine Liste von E-Mail-Adressen, von denen sich einige in meiner Tabelle befinden, andere nicht. Ich möchte alle E-Mail-Adressen aus dieser Liste auswählen, unabhängig davon, ob sie in der Tabelle enthalten sind oder nicht.

Ich kann die Benutzer, deren E-Mail-Adressen in der Tabelle enthalten sind, wie folgt abrufen:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

Aber wie kann ich in dieser Liste Werte auswählen, die nicht in der Tabelle vorhanden sind?

Außerdem, wie kann ich so auswählen:

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

Vielen Dank im Voraus.

Answer 1

Für 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)  

Für frühere Versionen können Sie etwas Ähnliches mit einer abgeleiteten Tabelle tun UNION ALL -der Konstanten.

/*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)

Oder wenn Sie nur die nicht vorhandenen (wie im Titel angedeutet) und nicht die in der Frage angegebene exakte Ergebnismenge haben möchten, können Sie einfach Folgendes tun

SELECT email
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  
EXCEPT
SELECT email
FROM Users

Answer 2

Sie müssen irgendwie eine Tabelle mit diesen Werten erstellen und dann NOT IN . Dies kann mit einer temporären Tabelle, einem CTE (Common Table Expression) oder einem Konstruktor für Tabellenwerte (verfügbar in SQL-Server 2008):

SELECT email
FROM
    ( VALUES 
        ('email1')
      , ('email2')
      , ('email3')
    ) AS Checking (email)
WHERE email NOT IN 
      ( SELECT email 
        FROM Users
      ) 

Das zweite Ergebnis lässt sich mit einer LEFT JOIN oder ein EXISTS Unterabfrage:

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

Sie sollten eine Tabelle mit der Liste der zu prüfenden E-Mails erhalten. Dann machen Sie diese Abfrage:

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

Wenn Sie die in der Datenbank gespeicherten E-Mails nicht in der Liste haben möchten, können Sie Folgendes tun:

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)

Auf diese Weise erhalten Sie ein Ergebnis wie

name | email  | emailadres | existence
-----|--------|------------|----------
NULL | NULL   | a@b.com    | Not exists
Jan  | j@j.nl | j@j.nl     | Exists

Die Verwendung der Operatoren IN oder EXISTS ist in diesem Fall schwerer als die linke Verknüpfung.

Viel Glück :)

Answer 5

Verwenden Sie diese : -- SQL Server 2008 oder höher

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