Как переписать IS DISTINCT FROM и НЕ ОТКАЗАТЬ ОТ?

Ответ 1

Предикат IS DISTINCT FROM был введен как функция T151 SQL: 1999, и его читаемое отрицание IS NOT DISTINCT FROM было добавлено как функция T152 SQL: 2003. Цель этих предикатов состоит в том, чтобы гарантировать, что результат сравнения двух значений будет либо True, либо False, никогда не неизвестным.

Эти предикаты работают с любым сопоставимым типом (включая строки, массивы и мультимножества), что делает их довольно сложными для их эмулирования. Однако SQL Server не поддерживает большинство этих типов, поэтому мы можем получить довольно далеко, проверив нулевые аргументы/операнды:

• a IS DISTINCT FROM b можно переписать как:

  ((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 можно переписать как:

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

Ваш собственный ответ неверен, поскольку он не считает, что FALSE OR NULL оценивается как Неизвестный. Например, NULL IS DISTINCT FROM NULL должен оцениваться как False. Аналогично, 1 IS NOT DISTINCT FROM NULL должен оцениваться как False. В обоих случаях ваши выражения имеют значение Unknown.

Ответ 2

Другое решение, которое мне нравится, использует истинный двухзначный логический результат EXISTS в сочетании с INTERSECT. Это решение должно работать в SQL Server 2005 +.

• a IS NOT DISTINCT FROM b может быть записана как:

  EXISTS(SELECT a INTERSECT SELECT b)

Как указано в документе, INTERSECT обрабатывает два значения NULL как равные, поэтому, если оба они имеют значение NULL, тогда INTERSECT выводит одну строку, поэтому EXISTS возвращает true.

• a IS DISTINCT FROM b может быть записана как:

  NOT EXISTS(SELECT a INTERSECT SELECT b)

Этот подход гораздо более краткий, если у вас есть несколько столбцов с нулевым значением, которые нужно сравнить в двух таблицах. Например, чтобы возвращать строки в TableB, которые имеют разные значения для Col1, Col2 или Col3, чем TableA, можно использовать следующее:

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

Пол Уайт объясняет это обходное решение более подробно: http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

Ответ 3

Если ваша реализация SQL не реализует операторы SQL IS DISTINCT FROM и IS NOT DISTINCT FROM SQL, вы можете переписать выражения, содержащие их, используя следующие эквивалентности:

В общем:

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

Этот ответ неверен при использовании в контексте, где имеет значение разница между UNKNOWN и FALSE. Я думаю, что это необычно. См. Принятый ответ @ChrisBandy.

Если значение метки может быть идентифицировано, которое фактически не встречается в данных, тогда COALESCE является альтернативой:

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

Ответ 4

Одно предостережение при перезаписи IS DISTINCT FROM и NOT DISTINCT FROM было бы не мешать использованию индексов, по крайней мере при использовании SQL Server. Другими словами, при использовании следующего:

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

SQL Server не сможет использовать какой-либо индекс, который содержит столбец. Таким образом, в предложении WHERE было бы предпочтительнее использовать форму

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

чтобы использовать любые индексы для столбца. (Parens используется только для ясности)

Ответ 5

Для справки самая каноническая (и читаемая) реализация IS [ NOT ] DISTINCT FROM будет хорошо отформатированным выражением CASE. Для 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

Очевидно, что другие решения (в частности John Keller's, используя INTERSECT), более кратки.

Подробнее здесь

Ответ 6

Просто чтобы продлить ответ Джона Келлера. Я предпочитаю использовать EXISTS и EXCEPT шаблон:

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

а также

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

по одной конкретной причине. NOT выравнивается, тогда как INTERSECT инвертируется.

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

Демоверсия DBFiddle

Ответ 7

Эти выражения могут быть хорошей заменой логики IS DISTINCT FROM и работать лучше, чем предыдущие примеры, потому что они в конечном итоге скомпилированы SQL-сервером в одно предикатное выражение, которое приведет к ок. на половину стоимости оператора на выражение фильтра. Они по существу такие же, как решения, предоставленные Крисом Банди, однако они используют вложенные функции ISNULL и NULLIF для выполнения базовых сравнений.

(... очевидно, что ISNULL может быть заменен COALESCE, если вы предпочитаете)

• a IS DISTINCT FROM b можно переписать как:

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

• a IS NOT DISTINCT FROM b можно переписать как:

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

Ответ 8

a IS NOT DISTINCT FROM b

можно переписать как:

(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

можно переписать как:

NOT (a IS NOT DISTINCT FROM b)