risposta-alla-domanda-sullo-sviluppo-web-bd.com

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

Sulla base della 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])))

Questo in pratica mi dirà cosa c'è nei miei dati di test che è non nei miei dati reali. Che è completamente bene per quello che devo fare.

sqlsql-serversql-server-2008sql-server-2005
162
Casey

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

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C 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 (A.C = B.C)
WHERE A.C IS NULL OR B.C 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 null.

181
treaschf
(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1)
218
erikkallen

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ò essere facilmente complicato 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.

36
Maisie John

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
;
9
bilelovitch

Se si desidera ottenere i valori di colonna diversi, è possibile 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
        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
)

ESEMPIO DI FISSO SQL

4
Roman Pekar

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)
3
studgeek

Questo farà il trucco, simile alla soluzione di Tiago , restituire 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.

2
Adrian-Bogdan Ionescu

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.

2
Kango_V

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

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

1
thomas398

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
0
Tiago Moutinho