SQL 2017 - Сравнение значений между двумя таблицами, где определенные значения могут быть NULL

Question

У меня есть следующие таблицы со следующими данными:

CREATE TABLE TestSource (
    InstrumentID int,
    ProviderID int,
    KPI1 int,
    Col2 varchar(255),
    KPI3 int
    );

CREATE TABLE TestTarget (
    InstrumentID int,
    ProviderID int,
    KPI1 int,
    Col2 varchar(255),
    KPI3 int 
    );

INSERT INTO TestSource (InstrumentID,ProviderID,KPI1,Col2,KPI3)
VALUES  (123, 27, 1, 'ABC', 10.0 ),
            (1234, 27, 2, 'DEF', 10.0 ),
            (345, 27, 1, NULL, 0.00 );

INSERT INTO TestTarget (InstrumentID,ProviderID,KPI1,Col2,KPI3)
VALUES  (123, 27, 1, 'ABC', 10.0 ),
            (1234, 27, 2, 'DEF', 10.0 ),
            (345, 27, 1, 'ABC', 0.0 );

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

DECLARE @Result NVARCHAR(max)

;WITH 

compare_source (InstrumentID,ProviderID,

/*** Source columns to compare ***/

            Col1Source, Col2Source,Col3Source

)

as (

              select     InstrumentID
                        ,ProviderID
                        ,KPI1
                        --,ISNULL(Col2,'NA') as Col2
                        ,Col2
                        ,KPI3

              from TestSource

              group by
                         InstrumentID
                        ,ProviderID
                        ,KPI1
                        ,Col2
                        ,KPI3
),

compare_target (InstrumentID,ProviderID,

/*** Target columns to compare ***/

            Col1Target,Col2Target,Col3Target

)

as 

(
            select
                     InstrumentID
                    ,ProviderID
                    ,KPI1
                    --,1
                    ,Col2
                    ,KPI3


            from TestTarget

            group by

                     InstrumentID
                    ,ProviderID
                    ,KPI1
                    ,Col2
                    ,KPI3
)

    SELECT @Result = STRING_AGG ('InstrumentID = ' + CONVERT(VARCHAR,InstrumentID)

         + ', Col1: ' + CONVERT(VARCHAR,Col1Source) + ' vs ' + CONVERT(VARCHAR,Col1Target)

        + ', Col2: ' + CONVERT(VARCHAR,Col2Source) + ' vs ' + CONVERT(VARCHAR,Col2Target)

        + ', Col3: ' + CONVERT(VARCHAR,Col3Source) + ' vs ' + CONVERT(VARCHAR,Col3Target) 

    , CHAR(13) + CHAR(10)

    )

FROM 
(
            select 
                     s.InstrumentID
                    ,s.Col1Source
                    ,t.Col1Target
                    ,s.Col2Source
                    ,t.Col2Target
                    ,s.Col3Source
                    ,t.Col3Target 

            from compare_source s

            left join compare_target t on t.InstrumentID = s.InstrumentID and t.ProviderID = s.ProviderID

            where not exists

            (
               select 1 from compare_target t where

                s.InstrumentID = t.InstrumentID AND 
              ( s.Col1Source   = t.Col1Target ) OR (ISNULL(s.Col1Source, t.Col1Target) IS NULL)  AND
              ( s.Col2Source   = t.Col2Target ) OR (ISNULL(s.Col2Source, t.Col2Target) IS NULL)  AND
              ( s.Col3Source   = t.Col3Target ) OR (ISNULL(s.Col3Source, t.Col3Target) IS NULL) 
        )

) diff

PRINT @Result

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

Я знаю, что я мог бы легко выполнить ISNULL для моих столбцов в моих отдельных выборках, однако я хотел бы сохранить его как можно более универсальным и выполнять только мои проверки сравнения и проверки NULL в моем последнем предложении WHERE сравнения NOT EXISTS.

Я также попробовал следующую логику в моей логике сравнения без успеха:

            (
                   select 1 from compare_target t where

                    s.InstrumentID = t.InstrumentID AND 
                  ( s.Col1Source   = t.Col1Target OR (s.Col1Source IS NULL AND t.Col1Target IS NULL) ) AND
                  ( s.Col2Source   = t.Col2Target OR (s.Col2Source IS NULL AND t.Col2Target IS NULL) ) AND
                  ( s.Col3Source   = t.Col3Target OR (s.Col3Source IS NULL AND t.Col3Target IS NULL) )
            )

Другая проблема, с которой я сталкиваюсь, заключается в том, что мой запрос не может различить форматы данных (например, он видит значение 0,00 как эквивалентное 0,0)

Я не совсем уверен, что мне не хватает.

Любая помощь, чтобы поставить меня на правильный путь, была бы великолепна.

Answer 1

Ну, две проблемы, которые я вижу, это:

1. Предложение WHERE в нижней части должно иметь дополнительные скобки, чтобы объединить ваши OR с вашими AND, чтобы порядок приоритетов был правильным:

     select 1 from compare_target t where
   
       s.InstrumentID = t.InstrumentID AND 
     (( s.Col1Source   = t.Col1Target ) OR (ISNULL(s.Col1Source, t.Col1Target) IS NULL))  AND
     (( s.Col2Source   = t.Col2Target ) OR (ISNULL(s.Col2Source, t.Col2Target) IS NULL))  AND
     (( s.Col3Source   = t.Col3Target ) OR (ISNULL(s.Col3Source, t.Col3Target) IS NULL)) 
   

2. Когда вы делаете это изменение, одна возвращаемая строка имеет значение NULL в столбце Col2Source. Таким образом, когда вы пытаетесь построить строку, которую вы отправляете в STRING_AGG, в его середине находится NULL. Таким образом, вся строка будет NULL. Поэтому вам нужно будет использовать ISNULL либо в подзапросе в предложении FROM, либо в STRING_AGG().... или, возможно, там, где вы его закомментировали.