Comment comparer les valeurs qui peuvent être null est T-SQL

Je veux m'assurer que je ne suis pas d'insérer une ligne en double dans ma table (par exemple, seule clé primaire différentes). Tout mon champ autoriser les valeurs NULL que j'ai décidé de la valeur null signifie "toutes les valeurs". En raison de la valeur null, la déclaration suivante dans ma procédure stockée ne peut pas fonctionner:

IF EXISTS(SELECT * FROM MY_TABLE WHERE 
    MY_FIELD1 = @IN_MY_FIELD1  AND
    MY_FIELD2 = @IN_MY_FIELD2  AND
    MY_FIELD3 = @IN_MY_FIELD3  AND 
    MY_FIELD4 = @IN_MY_FIELD4  AND
    MY_FIELD5 = @IN_MY_FIELD5  AND
    MY_FIELD6 = @IN_MY_FIELD6)
    BEGIN
        goto on_duplicate
    END

depuis NULL = NULL n'est pas vrai.

Comment puis-je vérifier les doublons sans avoir de SI EST NUL, pour chaque colonne?

Sont à la fois MY_FIELD{1,6} (de la table) et les variables @IN_MY_FILED{1,6} nullable ou est-ce juste MY_FIELD{1,6}?

InformationsquelleAutor srmark | 2009-07-02

48

Utilisation INTERSECT opérateur.

C'est NULL-sensible et efficace si vous avez un indice composite sur tous vos champs:
```
IF      EXISTS
        (
        SELECT  MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
        FROM    MY_TABLE
        INTERSECT
        SELECT  @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
        )
BEGIN
        goto on_duplicate
END
```
Notez que si vous créez un UNIQUE index sur les champs, votre vie sera beaucoup plus simple.
- Note INTERSECTION est disponible uniquement à partir de sql server à partir de 2005. Certains d'entre nous sont à quelques années en arrière 🙂
- Lorsque cette approche a été utilisée partiellement comme un prédicat de jointure pour un seul domaine, il a effectué beaucoup plus lent que l'équivalent "(a=b ou a est nulle et b est null))". Les champs a et b qui est arrivé à être sql_variants, c'est peut-être pourquoi.
InformationsquelleAutor Quassnoi
69

Dans le même sens que @Eric réponse, mais sans l'aide d'un 'NULL' symbole.
```
(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)
```
Ce n'est vrai que si les deux valeurs sont non-NULL, et l'égalité de l'autre, ou les deux valeurs sont NULL
- C'est certainement le plus serré pour l'Égalité des tests (A = B OR ISNULL(A, B) IS NULL sans l'inutile parens), mais il n'est pas sûr d'inverser en raison de la façon INCONNUE ne parvient pas à inverser. Le plus court de l'Inégalité de test que j'ai vu est @WileCau de l'.
- Cela permettra d'avoir des problèmes avec les index
- Oui, mais cela renvoie NULL si l'une ou les deux valeurs sont NULLES. Nous voulons quelque chose qui renvoie false si l'une ou les deux valeurs sont nulles.
InformationsquelleAutor Graeme Job
34

J'avais besoin d'une comparaison similaire lors d'une FUSION:
```
WHEN MATCHED AND (Target.Field1 <> Source.Field1 OR ...)
```
Les vérifications supplémentaires sont à éviter de mettre à jour les lignes où toutes les colonnes sont déjà les mêmes. Pour mes fins, je voulais NULL <> anyValue pour être Vrai, et NULL <> NULL être Faux.

La solution évolué comme suit:

Première tentative:
```
WHEN MATCHED AND
(
    (
        -- Neither is null, values are not equal
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NOT NULL
            AND Target.Field1 <> Source.Field1
    )
    OR
    (
        -- Target is null but source is not
        Target.Field1 IS NULL
            AND Source.Field1 IS NOT NULL
    )
    OR
    (
        -- Source is null but target is not
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NULL
    )

    -- OR ... Repeat for other columns
)
```
Deuxième tentative:
```
WHEN MATCHED AND
(
    -- Neither is null, values are not equal
    NOT (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND Target.Field1 <> Source.Field1

    -- Source xor target is null
    OR (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND NOT (Target.Field1 IS NULL AND Source.Field1 IS NULL)

    -- OR ... Repeat for other columns
)
```
Troisième tentative (inspiré par @Alors la réponse de):
```
WHEN MATCHED AND
(

    ISNULL(
        NULLIF(Target.Field1, Source.Field1),
        NULLIF(Source.Field1, Target.Field1)
    ) IS NOT NULL

    -- OR ... Repeat for other columns
)
```
La même ISNULL/NULLIF logique peut être utilisée pour tester l'égalité et de l'inégalité:
- De l'égalité: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
- Inequaltiy: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NOT NULL
Voici un SQL-Violon en montrant comment il fonctionne http://sqlfiddle.com/#!3/471d60/1
- J'ai été de tenter de résoudre ce avant de Googler et réussi à venir avec NULLIF(A, B) IS NULL AND NULLIF(B, A) IS NULL (pour l'Égalité) et non PAS avec la pagaille avec les parens de l'inégalité. Le vôtre est de 3 caractères plus court de l'Égalité et de 4 caractères plus court pour l'Inégalité, mais je pense que le mien est un peu plus facile à comprendre (même si c'est certainement par rapport à ce cas).
- C'est assez illisible, il est donc préférable de comparer ISNULLed avec impossible valeurs si vous le pouvez, mais parfois ce n'est pas une solution (c'est à dire lorsque l'on compare les NULLable les colonnes de bit). Ça serait bien si il y avait une solution intégrée, mais je pense qu'il y motivation idéologique de garder ce genre de chose de T-SQL pour empêcher les gens d'utiliser la valeur Null dans la "mauvaise" façon.
- Je pense que l'INTERSECT et except donne une belle méthodes pour faire de l'égalité/inégalité des recherches que les valeurs Null sont traités comme des valeurs...
- Magnifique réponse, mais pour moi, il montre comment 3-valeur logique est totalement inexcusable de l'abomination pour l'usage du monde réel, du moins sans un tas de nouveaux opérateurs qu'existe dans aucun dialecte SQL.
InformationsquelleAutor WileCau
32

Utilisation ISNULL:
```
ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')
```
Vous pouvez modifier 'NULL' à quelque chose comme 'All Values' si il est plus logique de le faire.

Il convient de noter que, avec deux arguments, ISNULL fonctionne de la même comme FUSIONNER, que vous pouvez utiliser si vous avez un peu de valeurs à tester (c'est à dire-COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')). COALESCE également de retour après le premier non-nulle, ce qui signifie qu'il est (un peu) plus vite si vous vous attendez à MY_FIELD1 être vide. Cependant, je trouve ISNULL beaucoup plus lisible, c'est pourquoi je l'ai utilisé, ici.
- Que faire si la valeur du champ est "NULLE"?
- Utiliser une autre valeur qui ne peut pas se produire, tels que 'x' pour un champ de numéro, ou une trop longue chaîne pour un champ de type chaîne.
- J'ai été en utilisant un stand en valeur. Évidemment, il doit être unique, non utilisé valeur dans le champ.
- À l'aide d'une trop longue chaîne ne fonctionnera pas, que ISNULL tronque la valeur de remplacement en fonction de la taille de la valeur cochée.
- cela ne fonctionne pas pour entier (ne peut pas convertir varchar int)
- Cela pourrait causer des problèmes pour Jennifer Null: gizmodo.com/...
InformationsquelleAutor Eric

IF EXISTS(SELECT * FROM MY_TABLE WHERE 
            (MY_FIELD1 = @IN_MY_FIELD1 
                     or (MY_FIELD1 IS NULL and @IN_MY_FIELD1 is NULL))  AND
            (MY_FIELD2 = @IN_MY_FIELD2 
                     or (MY_FIELD2 IS NULL and @IN_MY_FIELD2 is NULL))  AND
            (MY_FIELD3 = @IN_MY_FIELD3 
                     or (MY_FIELD3 IS NULL and @IN_MY_FIELD3 is NULL))  AND
            (MY_FIELD4 = @IN_MY_FIELD4 
                     or (MY_FIELD4 IS NULL and @IN_MY_FIELD4 is NULL))  AND
            (MY_FIELD5 = @IN_MY_FIELD5 
                     or (MY_FIELD5 IS NULL and @IN_MY_FIELD5 is NULL))  AND
            (MY_FIELD6 = @IN_MY_FIELD6
                     or (MY_FIELD6 IS NULL and @IN_MY_FIELD6 is NULL)))
            BEGIN
                    goto on_duplicate
            END

Verbeux par rapport à la IFNULL/FUSIONNER solution. Mais les travaux sans avoir à penser à ce que la valeur n'apparaîtra pas dans les données qui peuvent être utilisés comme le stand de la valeur NULL.

InformationsquelleAutor Shannon Severance

10

Vous pourrait fusionner chaque valeur, mais c'est un peu grincer des dents induisant:
```
    IF EXISTS(SELECT * FROM MY_TABLE WHERE 
    coalesce(MY_FIELD1,'MF1') = coalesce(@IN_MY_FIELD1,'MF1')  AND
    ...
    BEGIN
            goto on_duplicate
    END
```
Vous auriez également besoin de s'assurer que le coalesced valeur n'est pas une valeur valide sur la colonne en question. Par exemple, si il était possible que la valeur de MY_FIELD1 pourrait être "MF1", puis ce serait la cause de beaucoup de fausses hits.

InformationsquelleAutor butterchicken
7

Que si vous voulez faire une comparaison pour les valeurs qui ne SONT PAS égaux? Juste à l'aide d'un "NON" en face de, déjà mentionnés, les comparaisons ne fonctionne pas. Le meilleur que j'ai pu arriver, c'est:
```
(Field1 <> Field2) OR (NULLIF(Field1, Field2) IS NOT NULL) OR (NULLIF(Field2, Field1) IS NOT NULL)
```
InformationsquelleAutor Jamie G
6

Vous créer une clé primaire sur vos champs et de laisser le moteur d'assurer l'unicité. SI il EXISTE logique est incorrecte de toute façon comme c'est imparfait, avec des conditions de course.
- Ou d'une contrainte unique, puisqu'il est indiqué qu'il y a déjà une clé primaire
InformationsquelleAutor Remus Rusanu
3

Est égal à comparaison:
```
((f1 IS NULL AND f2 IS NULL) OR (f1 IS NOT NULL AND f2 IS NOT NULL AND f1 = f2))
```
Pas Égal À comparaison: il suffit de nier la est Égal à comparaison ci-dessus.
```
NOT ((f1 IS NULL AND f2 IS NULL) OR (f1 IS NOT NULL AND f2 IS NOT NULL AND f1 = f2))
```
Est-il détaillé? Oui, il est. Cependant, il est efficace car il n'a pas d'appeler n'importe quelle fonction. L'idée est d'utiliser de court-circuit dans les prédicats assurez-vous que l'opérateur égal (=) est utilisé uniquement avec les valeurs non null, null sinon se serait propagée vers le haut dans l'arborescence d'expression.
- Aussi explicite que c'est, c'est la meilleure réponse. De nombreuses autres réponses retourner la valeur NULL à la place de FALSE, et ce sera VRAI si niée.
InformationsquelleAutor drowa
2

Vous pouvez utiliser SET ANSI_NULLS afin de préciser le comportement du signe Égal (=) et n'est Pas Égal À (<>) les opérateurs de comparaison lorsqu'ils sont utilisés avec des valeurs null.

InformationsquelleAutor drowa
0

Vous devrez utiliser la valeur EST NULLE ou ISNULL. Il n'est pas vraiment loin autour d'elle.

InformationsquelleAutor William Edmondson
0

NULLIF(LA CIBLE.relation_id, SOURCE.app_relation_id) EST NULLE solution Simple
- Cela ne fonctionne pas si la première valeur est nulle, mais la seconde n'est pas une valeur.
InformationsquelleAutor user3763117
-2

Avez-vous vérifier NULLIF?
http://msdn.microsoft.com/en-us/library/ms177562.aspx
- Cela ne fonctionne pas dans le cas des OP cas. Les deux: nullif(cast(null comme varchar(max)), 'X') et nullif(cast(null comme varchar(max)), null) return null
InformationsquelleAutor THEn

Vous devez vous connecter pour publier un commentaire.