Sql запрос для возврата различий между двумя таблицами

Я пытаюсь сравнить две таблицы, SQL Server, для проверки некоторых данных. Я хочу вернуть все строки из обеих таблиц, где данные находятся либо в одном, либо в другом. По сути, я хочу показать все несоответствия. Мне нужно проверить три части данных при этом, FirstName, LastName и Product.

Я новичок в SQL, и кажется, что многие решения, которые я нахожу, усложняют ситуацию. Мне не нужно беспокоиться о NULL.

Я начал с чего-то вроде этого:

SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name] 
FROM [Real Data]))

У меня возникают проблемы с этим, хотя.

Спасибо!

EDIT:

Основываясь на ответе @treaschf, я пытаюсь использовать вариант следующего запроса:

SELECT td.[First Name], td.[Last Name], td.[Product Name]
FROM [Temp Test Data] td FULL OUTER JOIN [Data] AS d 
ON td.[First Name] = d.[First Name] AND td.[Last Name] = d.[Last Name] 
WHERE (d.[First Name] = NULL) AND (d.[Last Name] = NULL)

Но я продолжаю получать 0 результатов назад, когда я знаю, что есть не менее 1 строки в td, которая не находится в d.

EDIT:

Хорошо, думаю, я понял это. По крайней мере, за несколько минут тестирования, похоже, он работает достаточно хорошо.

SELECT [First Name], [Last Name]
FROM [Temp Test Data] AS td
WHERE (NOT EXISTS
        (SELECT [First Name], [Last Name]
         FROM [Data] AS d
         WHERE ([First Name] = td.[First Name]) OR ([Last Name] = td.[Last Name])))

Это, в основном, будет рассказывать мне, что в моих тестовых данных, которых нет в моих реальных данных. Что совершенно нормально для того, что мне нужно делать.

Ответы

Ответ 1

ЕСЛИ у вас есть таблицы A и B, оба с colum C, вот записи, которые присутствуют в таблице A, но не в B:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

Чтобы получить все различия с одним запросом, необходимо использовать полное соединение, например:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

В этом случае вам нужно знать, что когда запись может быть найдена в A, но не в B, чем столбцы, которые исходят от B, будут NULL и аналогично для них, которые присутствуют в B, а не в A, столбцы из A будут иметь значение null.

Ответ 2

(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1)

Ответ 3

Я знаю, что это не может быть популярным ответом, но я согласен с @Randy Minder в использовании стороннего инструмента, когда требуется более сложное сравнение.

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

Есть много таких инструментов, как ApexSQL Data Diff или Quest Toad, и вы всегда можете использовать их в пробном режиме, чтобы выполнить задание.

Ответ 4

Если вы хотите, чтобы значения столбца были разными, вы можете использовать модель Entity-Attribute-Value:

declare @Data1 xml, @Data2 xml

select @Data1 = 
(
    select * 
    from (select * from Test1 except select * from Test2) as a
    for xml raw('Data')
)

select @Data2 = 
(
    select * 
    from (select * from Test2 except select * from Test1) as a
    for xml raw('Data')
)

;with CTE1 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data1.nodes('Data/@*') as T(C)    
), CTE2 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data2.nodes('Data/@*') as T(C)     
)
select
    isnull(C1.ID, C2.ID) as ID, isnull(C1.Name, C2.Name) as Name, C1.Value as Value1, C2.Value as Value2
from CTE1 as C1
    full outer join CTE2 as C2 on C2.ID = C1.ID and C2.Name = C1.Name
where
not
(
    C1.Value is null and C2.Value is null or
    C1.Value is not null and C2.Value is not null and C1.Value = C2.Value
)

ПРИМЕР SQL FIDDLE

Ответ 5

Чтобы получить все различия между двумя таблицами, вы можете использовать, как я, этот запрос SQL:

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM Table1
      EXCEPT
      SELECT * FROM Table2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM Table2
      EXCEPT
      SELECT * FROM Table1
      ) AS T2
;

Ответ 6

Попробуйте следующее:

SELECT 
    [First Name], [Last Name]
FROM 
    [Temp Test Data] AS td EXCEPTION JOIN [Data] AS d ON 
         (d.[First Name] = td.[First Name] OR d.[Last Name] = td.[Last Name])

Намного проще читать.

Ответ 7

Это сделает трюк, аналогичный решению Tiago, также вернет таблицу "источник".

select [First name], [Last name], max(_tabloc) as _tabloc
from (
  select [First Name], [Last name], 't1' as _tabloc from table1
  union all
  select [First name], [Last name], 't2' as _tabloc from table2
) v
group by [Fist Name], [Last name]
having count(1)=1

результат будет содержать различия между таблицами, в столбце _tabloc у вас будет таблица.

Ответ 8

Простая вариация ответа @erikkallen, показывающая, в какой таблице находится строка:

(   SELECT 'table1' as source, * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT 'table2' as source, * FROM table2
    EXCEPT
    SELECT * FROM table1)

Ответ 9

Для простого smoke test, где вы пытаетесь обеспечить соответствие двух таблиц, не беспокоясь о именах столбцов:

--ensure tables have matching records
Select count (*) from tbl_A
Select count (*) from tbl_B

--create temp table of all records in both tables
Select * into #demo from tbl_A 
Union All
Select * from tbl_B

--Distinct #demo records = Total #demo records/2 = Total tbl_A records = total tbl_B records
Select distinct * from #demo

Вы можете легко написать процедуру хранения для сравнения партии таблиц.

Ответ 10

Существует проблема с производительностью, связанная с левым соединением, а также полное соединение с большими данными.

По-моему, это лучшее решение:

select [First Name], count(1) e from (select * from [Temp Test Data] union all select * from [Temp Test Data 2]) a group by [First Name] having e = 1