Вопрос по sql-server, sql – Как переписать ОТЛИЧАЕТСЯ ОТ, а НЕ ОТЛИЧАЕТСЯ ОТ?

44

Как переписать выражения, содержащие стандартныеIS DISTINCT FROM а такжеIS NOT DISTINCT FROM операторы в реализациях SQL, таких как Microsoft SQL Server 2008R2, которые их не поддерживают?

Ваш Ответ

8   ответов
0
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)
0

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

(... obviously ISNULL could be substituted with COALESCE if you prefer)

a IS DISTINCT FROM b can be rewritten as:

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

a IS NOT DISTINCT FROM b can be rewritten as:

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

1

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

Очевидно, другие решения (в частности,Джон Келлер, с помощьюINTERSECT) являются более краткими.

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

5

ОТ» заключается в том, чтобы не мешать использованию индексов, по крайней мере при использовании SQL Server. Другими словами, при использовании следующего:

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

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

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

использовать любые индексы дляcolumn, (Паренс используется только для ясности)

+1 за упоминание о том, как функции убивают использование индекса. Это то, как я попал сюда в первую очередь.
11

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

В общем:

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)
@JasonKresowaty: это вообще не редкость. В любом предикате, как(a IS DISTINCT FROM b) AND somethingРазличие междуUNKNOWN а такжеFALSE необходимо. Еслиa а такжеb обаNULLтогда твоя эмуляция сгенерируетNULLнезависимо от того, еслиsomething являетсяTRUE или жеFALSE.
По какой-то причине первый подход в этом ответе (с использованием логики) на порядок медленнее, чем второй (с использованием функций) для меня (SQL Server).
Да, этот ответ неверен при использовании в контексте, где разница между UNKNOWN и FALSE имеет значение. Я думаю, что это редко. Jason Kresowaty
Вы также можете использоватьcoalesce(a = b, a is null and b is null) проверить, совпадают ли они, таким образом (НЕ ОТЛИЧАЕТСЯ ОТ b)
Это неправильный ответ. См. Последний абзац в Chris & apos; ответ.
1

Джон Келлер ответ. Я предпочитаю использовать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 Demo

24

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

a IS NOT DISTINCT FROM b can be written as:

EXISTS(SELECT a INTERSECT SELECT b)

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

a IS DISTINCT FROM b can be written as:

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://web.archive.org/web/20180422151947/http://sqlblog.com:80/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

Отличный ответ, я предпочитаю это над принятым
Это должен быть принятый ответ, поскольку он переписывает предикат таким образом, чтобы не дублировать ссылки наa а такжеb, Для недетерминированных выраженийa а такжеbили выражения с побочными эффектами (такими как регистрация), которые были бы очень полезны. Ваш второй пример также подражает(A.Col1, A.Col2, A.Col3) IS DISTINCT FROM (B.Col1, B.Col2, B.Col3), который поддерживается только в PostgreSQL (насколько мне известно). Иногда очень полезный предикат.
37

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

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

a IS DISTINCT FROM b can be rewritten as:

((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 can be rewritten as:

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

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

((a & lt; & gt; b ИЛИ НЕДЕЙСТВУЕТ ИЛИ b НУЛЯЕТ) И НЕ (А НУЛЯЕТ, И Б НУЛ))

Похожие вопросы