consulta sql para retornar diferenças entre duas tabelas

Eu estou tentando comparar duas tabelas, SQL Server, para verificar alguns dados. Eu quero retornar todas as linhas de ambas as tabelas onde os dados estão em um ou no outro. Em essência, quero mostrar todas as discrepâncias. Preciso verificar três partes de dados ao fazer isso, FirstName, LastName e Product.

Sou relativamente novo no SQL e parece que muitas das soluções que estou encontrando estão complicando demais as coisas. Eu não tenho que me preocupar com NULLs.

Comecei tentando algo assim:

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

Estou tendo problemas para levar isso adiante ainda.

Obrigado!

EDITAR:

Com base na resposta de @treaschf, tentei usar uma variação da seguinte consulta:

 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) 

Mas eu continuo recebendo 0 resultados de volta, quando eu sei que há pelo menos 1 linha no td que não está em d.

EDITAR:

Ok, acho que descobri. Pelo menos nos meus poucos minutos de teste, parece funcionar bem o suficiente.

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

Isso basicamente vai me dizer o que está nos meus dados de teste que não estão em meus dados reais. O que é completamente bom para o que preciso fazer.

Se você tiver as tabelas A e B , ambas com a coluna C , aqui estão os registros, que estão presentes na tabela A mas não na B :

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

Para obter todas as diferenças com uma única consulta, uma junit completa deve ser usada, assim:

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

O que você precisa saber neste caso é que, quando um registro pode ser encontrado em A , mas não em B , as colunas que vêm de B serão NULL e, da mesma forma, as que estão presentes em B e não em A , as colunas de A serão nulas.

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

Eu sei que isso pode não ser uma resposta popular, mas eu concordo com o @Randy Minder em usar ferramentas de terceiros quando uma comparação mais complexa é necessária.

Este caso específico é fácil e, neste caso, tais ferramentas não são necessárias, mas isso pode se tornar complexo facilmente se você introduzir mais colunas, bancos de dados em dois servidores, critérios de comparação mais complexos e tal.

Existem muitas dessas ferramentas, como o ApexSQL Data Diff ou o Quest Toad, e você sempre pode usá-las no modo de teste para realizar o trabalho.

Para obter todas as diferenças entre duas tabelas, você pode usar como eu esta solicitação 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 ; 

Se você deseja obter quais valores de coluna são diferentes, você pode usar o modelo de valor de atributo de entidade:

 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 TCvalue('../@ID', 'bigint') as ID, TCvalue('local-name(.)', 'nvarchar(128)') as Name, TCvalue('.', 'nvarchar(max)') as Value from @Data1.nodes('Data/@*') as T(C) ), CTE2 as ( select TCvalue('../@ID', 'bigint') as ID, TCvalue('local-name(.)', 'nvarchar(128)') as Name, TCvalue('.', '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 ) 

EXEMPLO FIDDLE SQL

Tente isto:

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

Muito mais simples de ler.

Isso fará o truque, similar à solução de Tiago , também retorna a tabela “source”.

 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 

result conterá diferenças entre tabelas, na coluna _tabloc você terá referência de tabela.

Para um teste de fumaça simples em que você está tentando garantir que duas tabelas correspondam sem preocupação sobre os nomes das colunas:

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

Você pode facilmente escrever um procedimento de armazenamento para comparar um lote de tabelas.

Variação simples na resposta @erikkallen que mostra em qual tabela a linha está presente:

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

Se você receber um erro

Todas as consultas combinadas usando um operador UNION, INTERSECT ou EXCEPT devem ter um número igual de expressões em suas listas de destino.

então isso pode ajudar a adicionar

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

Há um problema de desempenho relacionado à junit esquerda, bem como a junit completa com dados grandes.

Na minha opinião, esta é a melhor solução:

 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