query SQL per restituire le differenze tra due tabelle

Sto cercando di confrontare due tabelle, SQL Server, per verificare alcuni dati. Voglio restituire tutte le righe da entrambe le tabelle in cui i dati sono nell’uno o nell’altro. In sostanza, voglio mostrare tutte le discrepanze. Ho bisogno di controllare tre pezzi di dati in questo modo, FirstName, LastName e Product.

Sono abbastanza nuovo per SQL e sembra che molte delle soluzioni che sto trovando finiscano per complicare le cose. Non devo preoccuparmi dei NULL.

Ho iniziato provando qualcosa del genere:

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

Sto avendo problemi a prendere questo ulteriormente però.

Grazie!

MODIFICARE:

In base alla risposta di @treaschf, ho cercato di utilizzare una variante della seguente query:

 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) 

Ma continuo a ottenere 0 risultati, quando so che c’è almeno 1 riga in td che non è in d.

MODIFICARE:

Ok, penso di aver capito. Almeno nei miei pochi minuti di test sembra funzionare abbastanza bene.

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

In pratica questo mi dirà cosa c’è nei miei dati di test che non è nei miei dati reali. Che è completamente bene per quello che devo fare.

Se hai le tabelle A e B , entrambe con la colonna C , ecco i record, che sono presenti nella tabella A ma non in B :

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

Per ottenere tutte le differenze con una singola query, è necessario utilizzare un join completo, come questo:

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

Quello che devi sapere in questo caso è che quando un record può essere trovato in A , ma non in B , che le colonne che provengono da B saranno NULL, e allo stesso modo per quelle che sono presenti in B e non in A , le colonne da A saranno nulle.

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

So che questa potrebbe non essere una risposta popolare, ma concordo con @Randy Minder sull’utilizzo di strumenti di terze parti quando è necessario un confronto più complesso.

Questo caso specifico qui è facile e in questo caso tali strumenti non sono necessari, ma questo può diventare complesso facilmente se si introducono più colonne, database su due server, criteri di confronto più complessi e così via.

Ci sono molti di questi strumenti come ApexSQL Data Diff o Quest Toad e puoi sempre usarli in modalità di prova per portare a termine il lavoro.

Per ottenere tutte le differenze tra due tabelle, puoi usare come me questa richiesta 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 si desidera ottenere i valori di colonna diversi, è ansible utilizzare il modello 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 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 ) 

SQL FIDDLE EXAMPLE

Prova questo :

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

Molto più semplice da leggere.

Questo farà il trucco, simile alla soluzione di Tiago , restituirà anche la tabella “fonte”.

 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 

il risultato conterrà le differenze tra le tabelle, nella colonna _tabloc avrai il riferimento alla tabella.

Per un semplice test del fumo in cui stai cercando di garantire che due tavoli combacino senza preoccuparti dei nomi delle colonne:

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

È ansible scrivere facilmente una procedura di archiviazione per confrontare un gruppo di tabelle.

Semplice variazione sulla risposta di @erikkallen che mostra in quale tabella è presente la riga:

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

Se ricevi un errore

Tutte le query combinate utilizzando un operatore UNION, INTERSECT o EXCEPT devono avere un numero uguale di espressioni nei rispettivi elenchi di destinazione.

quindi potrebbe essere utile aggiungere

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

Esiste un problema di prestazioni relativo al join sinistro e un join completo con dati di grandi dimensioni.

Secondo me questa è la soluzione migliore:

 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