Comment réécrire IS DISTINCT FROM et IS NOT DISTINCT FROM dans SQL Server 20008R2 ?

Question

Comment réécrire des expressions contenant la norme IS DISTINCT FROM y IS NOT DISTINCT FROM des opérateurs dans l'implémentation SQL de Microsoft SQL Server 2008R2 qui ne les prend pas en charge ?

Answer 1

En IS DISTINCT FROM a été introduit en tant que caractéristique T151 de SQL:1999, et sa négation lisible, IS NOT DISTINCT FROM a été ajoutée en tant que caractéristique T152 de SQL:2003. Le but de ces prédicats est de garantir que le résultat de la comparaison de deux valeurs est soit Véritable ou Faux jamais Inconnu .

Ces prédicats fonctionnent avec n'importe quel type comparable (y compris les lignes, les tableaux et les multisets), ce qui rend assez compliqué leur émulation exacte. Cependant, SQL Server ne prend pas en charge la plupart de ces types, ce qui nous permet d'aller assez loin en vérifiant les arguments/opérandes nuls :

• a IS DISTINCT FROM b peut être réécrit comme suit :

  ((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL))

• a IS NOT DISTINCT FROM b peut être réécrit comme suit :

  (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))

---

Votre propre réponse est incorrecte car elle ne tient pas compte du fait que FALSE OR NULL évalue à Inconnu . Par exemple, NULL IS DISTINCT FROM NULL devrait évaluer à Faux . De même, 1 IS NOT DISTINCT FROM NULL devrait évaluer à Faux . Dans les deux cas, vos expressions donnent Inconnu .

Answer 2

Une autre solution que j'aime bien exploite le véritable résultat booléen à deux valeurs de EXISTS combiné à INTERSECT. Cette solution devrait fonctionner dans SQL Server 2005+.

• a IS NOT DISTINCT FROM b peut s'écrire comme suit :

  EXISTS(SELECT a INTERSECT SELECT b)

Comme documenté, INTERSECT traite deux valeurs NULL comme égales, donc si les deux sont NULL, alors INTERSECT donne une seule ligne, donc EXISTS donne vrai.

• a IS DISTINCT FROM b peut s'écrire comme suit :

  NOT EXISTS(SELECT a INTERSECT SELECT b)

Cette approche est beaucoup plus concise si vous avez plusieurs colonnes annulables que vous devez comparer dans deux tables. Par exemple, pour renvoyer les lignes de la TableB dont les valeurs de Col1, Col2 ou Col3 sont différentes de celles de la TableA, on peut utiliser la méthode suivante :

SELECT *
FROM TableA A
   INNER JOIN TableB B ON A.PK = B.PK
WHERE NOT EXISTS(
   SELECT A.Col1, A.Col2, A.Col3
   INTERSECT
   SELECT B.Col1, B.Col2, B.Col3);

Paul White explique cette solution de contournement plus en détail : https://sql.kiwi/2011/06/undocumented-query-plans-equality-comparisons.html

Answer 3

Si votre implémentation SQL n'implémente pas la norme SQL IS DISTINCT FROM y IS NOT DISTINCT FROM vous pouvez réécrire les expressions qui les contiennent en utilisant les équivalences suivantes :

En général :

a IS DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NOT NULL)
OR
    ((a) IS NOT NULL AND (b) IS NULL)
OR
    ((a) <> (b))
)

a IS NOT DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NULL)
OR
    ((a) = (b))
)

Cette réponse est incorrecte lorsqu'elle est utilisée dans un contexte où la différence entre INCONNU et FAUX compte. Je pense que c'est cependant peu fréquent. Voir la réponse acceptée de @ChrisBandy.

S'il est possible d'identifier une valeur de remplacement qui n'apparaît pas réellement dans les données, alors COALESCE est une alternative :

a IS DISTINCT FROM b <==> COALESCE(a, placeholder) <> COALESCE(b, placeholder)
a IS NOT DISTINCT FROM b <==> COALESCE(a, placeholder) = COALESCE(b, placeholder)

Answer 4

Une mise en garde concernant la réécriture de IS DISTINCT FROM et IS NOT DISTINCT FROM serait de ne pas interférer avec l'utilisation des index, du moins lorsqu'on utilise SQL Server. En d'autres termes, lorsque vous utilisez ce qui suit :

WHERE COALESCE(@input, x) = COALESCE(column, x)

SQL Server ne sera pas en mesure d'utiliser un index qui inclut colonne . Ainsi, dans une clause WHERE, il serait préférable d'utiliser la forme

WHERE @input = column OR (@input IS NULL AND column IS NULL)

pour tirer parti de tout index pour colonne . (Parens utilisés uniquement pour la clarté)

Answer 5

L'épeler en utilisant CASE

Pour la référence, la mise en œuvre la plus canonique (et la plus lisible) de l'option IS [ NOT ] DISTINCT FROM serait un fichier bien formaté CASE expression. Pour IS DISTINCT FROM :

CASE WHEN [a] IS     NULL AND [b] IS     NULL THEN 0 -- FALSE
     WHEN [a] IS     NULL AND [b] IS NOT NULL THEN 1 -- TRUE
     WHEN [a] IS NOT NULL AND [b] IS     NULL THEN 1 -- TRUE
     WHEN [a] =               [b]             THEN 0 -- FALSE
     ELSE                                          1 -- TRUE
END

Évidemment, d'autres solutions (notamment de John Keller en utilisant INTERSECT ) sont plus concises.

Plus de détails ici .

Utilisation de DECODE si disponible

Je sais que cette question concerne SQL Server, mais pour être complet, Db2 et Oracle supportent une DECODE() dans le cas duquel on peut émuler ce qui suit :

-- a IS DISTINCT FROM b
DECODE(a, b, 1, 0) = 0

-- a IS NOT DISTINCT FROM b
DECODE(a, b, 1, 0) = 1