Как сравнивать значения, которые могут быть равны нулю, является T-SQL

Я хочу убедиться, что я не вставляю повторяющуюся строку в свою таблицу (например, только для первичного ключа). Все мое поле разрешает NULLS, поскольку я решил, что null означает "все значения". Из-за нулей следующий оператор в моей хранимой процедуре не может работать:

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

поскольку NULL = NULL неверно.

Как проверить дубликаты без инструкции IF NULL для каждого столбца?

Ответы

Ответ 1

Используйте INTERSECT.

It NULL - чувствительный и эффективный, если у вас есть сводный индекс во всех ваших полях:

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

Обратите внимание: если вы создадите индекс UNIQUE в своих полях, ваша жизнь будет намного проще.

Ответ 2

В том же виде, что и @Eric answer, но без символа 'NULL'.

(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)

Это будет верно, только если оба значения non-NULL и равны друг другу, или оба значения NULL

Ответ 3

Используйте ISNULL:

ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')

Вы можете изменить 'NULL' на что-то вроде 'All Values', если это имеет смысл.

Следует отметить, что с двумя аргументами ISNULL работает так же, как COALESCE, который вы можете использовать, если у вас есть несколько значений для проверки (т.е. - COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')). COALESCE также возвращается после первого ненулевого значения, что означает, что он (незначительно) быстрее, если вы ожидаете, что MY_FIELD1 будет пустым. Тем не менее, я нахожу ISNULL гораздо более удобочитаемым, поэтому я использовал его здесь.

Ответ 4

Мне нужно было аналогичное сравнение при выполнении MERGE:

WHEN MATCHED AND (Target.Field1 <> Source.Field1 OR ...)

Дополнительные проверки состоят в том, чтобы избежать обновления строк, где все столбцы уже совпадают. Для моих целей я хотел, чтобы NULL <> anyValue был True, а NULL <> NULL - False.

Решение развивалось следующим образом:

Первая попытка:

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
)

Вторая попытка:

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
)

Третья попытка (вдохновленная @THEn ответом):

WHEN MATCHED AND
(

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

    -- OR ... Repeat for other columns
)

Та же логика ISNULL/NULLIF может использоваться для проверки равенства и неравенства:

  • Равенство: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
  • Inequaltiy: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NOT NULL

Вот SQL-скрипт, демонстрирующий, как он работает http://sqlfiddle.com/#!3/471d60/1

Ответ 5

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

Wordy По сравнению с решением IFNULL/COALESCE. Но будет работать, не задумываясь о том, какое значение не будет отображаться в данных, которые могут использоваться в качестве подставки для NULL.

Ответ 6

Вы можете объединить каждое значение, но это немного взволнованно:

    IF EXISTS(SELECT * FROM MY_TABLE WHERE 
    coalesce(MY_FIELD1,'MF1') = coalesce(@IN_MY_FIELD1,'MF1')  AND
    ...
    BEGIN
            goto on_duplicate
    END

Вам также необходимо убедиться, что значение coalesced не является действительным значением в соответствующем столбце. Например, если возможно, что значение MY_FIELD1 может быть "MF1", тогда это вызовет множество ложных обращений.

Ответ 7

Вы создаете первичный ключ в своих полях и позволяете движку применять уникальность. В любом случае логика IF EXISTS неверна, так как является ошибочной в условиях гонки.

Ответ 8

Что делать, если вы хотите сделать сравнение для значений, которые НЕ равны? Просто использование "НЕ" перед предыдущими сравнениями не работает. Лучшее, что я мог придумать, это:

(Field1 <> Field2) OR (NULLIF(Field1, Field2) IS NOT NULL) OR (NULLIF(Field2, Field1) IS NOT NULL)

Ответ 9

NULLIF (TARGET.relation_id, SOURCE.app_relation_id) IS NULL Простое решение

Ответ 10

Вам нужно будет использовать IS NULL или ISNULL. Там действительно нет никого.

Ответ 11

Вы можете использовать SET ANSI_NULLS, чтобы указать поведение операторов сравнения Equals (=) и Not Equal To (< > ), когда они используются с нулевыми значениями.