sql - soporta - ¿Cómo reescribir ES DISTINTO DESDE Y NO DISTINTO DESDE?

optimizar consultas mysql explain

Question

¿Cómo se vuelven a escribir las expresiones que contienen los operadores estándar IS DISTINCT FROM y IS NOT DISTINCT FROM en las implementaciones de SQL, como Microsoft SQL Server 2008R2, que no las admiten?

Answer 1

El predicado IS DISTINCT FROM se introdujo como característica T151 de SQL: 1999, y su negación legible, IS NOT DISTINCT FROM , se agregó como característica T152 de SQL: 2003. El propósito de estos predicados es garantizar que el resultado de comparar dos valores sea Verdadero o Falso , nunca Desconocido .

Estos predicados funcionan con cualquier tipo comparable (incluidas filas, matrices y conjuntos múltiples), lo que hace que sea bastante complicado emularlos exactamente. Sin embargo, SQL Server no admite la mayoría de estos tipos, por lo que podemos llegar bastante lejos al verificar los argumentos / operandos nulos:

• a IS DISTINCT FROM b se puede reescribir como:

  ((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 se puede reescribir como:

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

Su propia respuesta es incorrecta ya que no considera que FALSE OR NULL evalúe como Desconocido . Por ejemplo, NULL IS DISTINCT FROM NULL debe evaluar en False . De manera similar, 1 IS NOT DISTINCT FROM NULL debe evaluar como Falso . En ambos casos, tus expresiones producen Desconocido .

Answer 2

Estas expresiones pueden ser un buen sustituto de la lógica de DISTINTO DESDE y es mejor que los ejemplos anteriores, ya que terminan siendo compiladas por el servidor SQL en una única expresión de predicado que resultará en aprox. La mitad del costo del operador en una expresión de filtro. Son esencialmente las mismas que las soluciones provistas por Chris Bandy, sin embargo, utilizan funciones ISNULL y NULLIF anidadas para realizar las comparaciones subyacentes.

(... obviamente ISNULL podría ser sustituido por COALESCE si lo prefiere)

• a IS DISTINCT FROM b se puede reescribir como:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NOT NULL

• a IS NOT DISTINCT FROM b se puede reescribir como:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NULL

Answer 3

Otra solución que me gusta aprovecha el verdadero resultado booleano de dos valores de EXISTS combinado con INTERSECT. Esta solución debería funcionar en SQL Server 2005+.

• a IS NOT DISTINCT FROM b se puede escribir como:

  EXISTS(SELECT a INTERSECT SELECT b)

Como se documentó, INTERSECT trata dos valores NULL como iguales, por lo tanto, si ambos son NULL, entonces INTERSECT da como resultado una sola fila, por lo que EXISTS arroja verdadero.

• a IS DISTINCT FROM b puede escribirse como:

  NOT EXISTS(SELECT a INTERSECT SELECT b)

Este enfoque es mucho más conciso si tiene varias columnas anulables que necesita comparar en dos tablas. Por ejemplo, para devolver filas en la Tabla B que tienen valores diferentes para Col1, Col2 o Col3 que la Tabla A, se puede usar lo siguiente:

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 explica esta solución en más detalle: http://web.archive.org/web/20180422151947/http://sqlblog.com:80/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

Answer 4

Para la referencia, la implementación más canónica (y legible) de IS [ NOT ] DISTINCT FROM sería una expresión CASE bien formateada. IS DISTINCT FROM :

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

Obviamente, otras soluciones (específicamente John Keller , usando INTERSECT ) son más concisas.

Más detalles aquí

Answer 5

Si su implementación de SQL no implementa los operadores de la norma SQL IS DISTINCT FROM y IS NOT DISTINCT FROM , puede volver a escribir las expresiones que los contienen utilizando las siguientes equivalencias:

En general:

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

Esta respuesta es incorrecta cuando se usa en un contexto donde la diferencia entre DESCONOCIDO y FALSO es importante. Aunque creo que eso es poco común. Vea la respuesta aceptada por @ChrisBandy.

Si se puede identificar un valor de marcador de posición que en realidad no aparece en los datos, entonces COALESCE es una alternativa:

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

Answer 6

Solo para extender la respuesta de John Keller . Prefiero usar el patrón EXISTS y EXCEPT :

a IS DISTINCT FROM b
<=>
EXISTS (SELECT a EXCEPT SELECT b)
-- NOT EXISTS (SELECT a INTERSECT SELECT b)

y

a IS NOT DISTINCT FROM b
<=>
NOT EXISTS (SELECT a EXCEPT SELECT b)
-- EXISTS (SELECT a INTERSECT SELECT b)

por una razón en particular NOT se alinea mientras que con INTERSECT se invierte.

SELECT 1 AS PK, 21 AS c, NULL AS b
INTO tab1;
SELECT 1 AS PK, 21 AS c, 2 AS b
INTO tab2;
SELECT *
FROM tab1 A
JOIN tab2 B ON A.PK = B.PK
WHERE EXISTS(SELECT A.c, A.B
EXCEPT
SELECT B.c, B.b);

Demostración de DBFiddle

Answer 7

Una advertencia en la reescritura ES DISTINTO DE y NO SE DISTINTA DE no interferir con el uso de índices, al menos cuando se usa SQL Server. En otras palabras, al usar lo siguiente:

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

SQL Server no podrá utilizar ningún índice que incluya columna . Por lo tanto, en una cláusula WHERE, sería preferible utilizar el formulario

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

Para aprovechar cualquier índice de columna . (Parens solo se usa para mayor claridad)

Answer 8

a IS NOT DISTINCT FROM b

Se puede reescribir como

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

a IS DISTINCT FROM b

Se puede reescribir como

NOT (a IS NOT DISTINCT FROM b)