SQL comparar dados de duas tabelas

Você deve ser capaz de "menos" ou "exceto" dependendo do sabor de SQL usado pelo seu DBMS.

select * from tableA
minus
select * from tableB

Se a consulta não devolve linhas, então os dados são exactamente os mesmos.

Usando operadores relacionais:

SELECT * FROM TableA
UNION 
SELECT * FROM TableB
EXCEPT 
SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;

Mudar EXCEPT para MINUS para Oráculo.

Ponto ligeiramente exigente: o acima depende da precedência do operador, que de acordo com a norma SQL é dependente da implementação, por isso MMMV. Ele funciona para o servidor SQL, para o qual a precedência é:

1. expressões entre parênteses
2. INTERSECT
3. EXCEPT e UNION avaliados da esquerda para a direita.

SELECT col1, col2, col3
FROM
(SELECT * FROM tableA
UNION ALL  
SELECT * FROM tableB) data
GROUP BY col1, col2, col3
HAVING count(*)!=2

SELECT c.ID
FROM clients c
WHERE EXISTS(SELECT c2.ID 
FROM clients2 c2
WHERE c2.ID = c.ID);

Devolverá todos os ids que são os mesmos em ambas as tabelas. Para obter a mudança de diferenças existe para não existir.

Tomando o script de um dia em que, eu modifiquei-o para também mostrar de que Tabela cada entrada vem.

DECLARE @table1 NVARCHAR(80)= 'table 1 name'
DECLARE @table2 NVARCHAR(80)= 'table 2 name'
DECLARE @sql NVARCHAR (1000)

SET @sql = 
'
SELECT ''' + @table1 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + '
) x

UNION 

SELECT ''' + @table2 + ''' AS table_name,* FROM 
(
SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 + '
) y
'

EXEC sp_executesql @stmt = @sql

Apenas para completar, um proc armazenado usando exceto o método para comparar 2 tabelas e dar resultado na mesma tabela com 3 erros status, adicionar, DEL, GAP a tabela deve ter o mesmo PK, você declara as 2 tabelas e campos para comparar 1 ou ambas as tabelas

Usa assim. ps_TableGap 'tbl1','Tbl2','fld1,fld2,fld3','fld4'fld5'fld6' (opcional)

/****** Object:  StoredProcedure [dbo].[ps_TableGap]    Script Date: 10/03/2013 16:03:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:       Arnaud ALLAVENA
-- Create date: 03.10.2013
-- Description: Compare tables
-- =============================================
create PROCEDURE [dbo].[ps_TableGap]
    -- Add the parameters for the stored procedure here
    @Tbl1 as varchar(100),@Tbl2 as varchar(100),@Fld1 as varchar(1000), @Fld2 as varchar(1000)= ''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    SET NOCOUNT ON;
--Variables
--@Tbl1 = table 1
--@Tbl2 = table 2
--@Fld1 = Fields to compare from table 1
--@Fld2 Fields to compare from table 2
Declare @SQL varchar(8000)= '' --SQL statements
Declare @nLoop int = 1 --loop counter
Declare @Pk varchar(1000)= '' --primary key(s) 
Declare @Pk1 varchar(1000)= '' --first field of primary key
declare @strTmp varchar(50) = '' --returns value in Pk determination
declare @FldTmp varchar (1000) = '' --temporarily fields for alias calculation

--If @Fld2 empty we take @Fld1
--fields rules: fields to be compare must be in same order and type - always returns Gap
If @Fld2 = '' Set @Fld2 = @Fld1

--Change @Fld2 with Alias prefix xxx become _xxx 
while charindex(',',@Fld2)>0
begin
    Set @FldTmp = @FldTmp + (select substring(@Fld2,1,charindex(',',@Fld2)-1) + ' as _' + substring(@Fld2,1,charindex(',',@Fld2)-1) + ',')
    Set @Fld2 = (select ltrim(right(@Fld2,len(@Fld2)-charindex(',',@Fld2))))
end
Set @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2
Set @Fld2 = @FldTmp

--Determinate primary key jointure
--rule: same pk in both tables
Set @nLoop = 1
Set @SQL = 'Declare crsr cursor for select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '''
 + @Tbl1 + ''' or TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 +  ''' or TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 
 + ''' order by ORDINAL_POSITION'
exec(@SQL)
open crsr 
fetch next from crsr into @strTmp
while @@fetch_status = 0
begin 
    if @nLoop = 1 
    begin 
        Set @Pk = 's.' + @strTmp + ' = b._' + @strTmp
        Set @Pk1 = @strTmp
        set @nLoop = @nLoop + 1 
    end 
    Else
    Set @Pk = @Pk + ' and s.' + @strTmp + ' = b._' + @strTmp
fetch next from crsr into @strTmp 

end 
close crsr
deallocate crsr

--SQL statement build
set @SQL = 'select case when s.' + @Pk1 + ' is null then ''Del'' when b._' + @Pk1 + ' is null then ''Add'' else ''Gap'' end as TypErr, '''
set @SQL = @SQL + @Tbl1 +''' as Tbl1, s.*, ''' + @Tbl2 +''' as Tbl2 ,b.* from (Select ' + @Fld1 + ' from ' + @Tbl1
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld2 + ' from ' + @Tbl2 + ')s full join (Select ' + @Fld2 + ' from ' + @Tbl2 
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld1 + ' from ' + @Tbl1 +')b on '+ @Pk 

--Run SQL statement
Exec(@SQL)
END

select * from
(
    select * from tableA
    minus
    select * from tableB
)
union all
select * from
(
    select * from tableB
    minus
    select * from tableA
)

    SELECT unnest(ARRAY[1,2,2,3,3]) 
    EXCEPT
    SELECT unnest(ARRAY[1,1,2,3,3])
UNION
    SELECT unnest(ARRAY[1,1,2,3,3])
    EXCEPT
    SELECT unnest(ARRAY[1,2,2,3,3])

O resultado é nulo, mas as fontes são diferentes!

Mas:

(
    SELECT unnest(ARRAY[1,2,2,3])
    EXCEPT ALL
    SELECT unnest(ARRAY[2,1,2,3])
)
UNION
(
    SELECT unnest(ARRAY[2,1,2,3])
    EXCEPT ALL
    SELECT unnest(ARRAY[1,2,2,3])
)

Funciona.

Eu tinha este mesmo problema no servidor SQL e escrevi este script T-SQL para automatizar o processo (na verdade esta é a versão diluída, o meu escreveu Todo o diff para uma única tabela para relatórios fáceis).

Actualize 'MyTable' e 'MyOtherTable' para os nomes das tabelas que deseja comparar.

DECLARE @ColName varchar(100)
DECLARE @Table1 varchar(100) = 'MyTable'
DECLARE @Table2 varchar(100) = 'MyOtherTable'


IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT  IDENTITY(INT, 1, 1) RowNum , c.name
INTO    #col
FROM    SYS.Objects o 
        JOIN SYS.columns c on o.object_id = c.object_id
WHERE   o.name = @Table1 AND NOT c.Name IN ('List','Columns','YouWantToIgnore')

DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)

    WHILE @Counter > 0

        BEGIN
            SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
                EXEC ('SELECT  t1.Identifier
                        ,t1.'+@ColName+' AS '+@Table1+@ColName+'
                        ,t2.'+@ColName+' AS '+@Table2+@ColName+'
                FROM    '+@Table1+' t1
                        LEFT JOIN '+@Table2+' t2 ON t1.Identifier = t2.Identifier 
                WHERE   t1.'+@ColName+' <> t2.'+@ColName)
            SET @Counter = @Counter - 1 
        END

É necessário que ambas as tabelas tenham uma chave primária, mas você pode colocá-la no tempdb com uma coluna de identidade se as tabelas de origem não tiverem uma.

declare @TableA_ThreePartName nvarchar(max) = ''
declare @TableB_ThreePartName nvarchar(max) = ''
declare @KeyName nvarchar(max) = ''

/***********************************************************************************************

    Script to compare two tables and return differneces in schema and data.

    Author: Devin Lamothe       2017-08-11

***********************************************************************************************/
set nocount on

-- Split three part name into database/schema/table
declare @Database_A nvarchar(max) = (
    select  left(@TableA_ThreePartName,charindex('.',@TableA_ThreePartName) - 1))
declare @Table_A nvarchar(max) = (
    select  right(@TableA_ThreePartName,len(@TableA_ThreePartName) - charindex('.',@TableA_ThreePartName,len(@Database_A) + 2)))
declare @Schema_A nvarchar(max) = (
    select  replace(replace(@TableA_ThreePartName,@Database_A + '.',''),'.' + @Table_A,''))

declare @Database_B nvarchar(max) = (
    select  left(@TableB_ThreePartName,charindex('.',@TableB_ThreePartName) - 1))
declare @Table_B nvarchar(max) = (
    select  right(@TableB_ThreePartName,len(@TableB_ThreePartName) - charindex('.',@TableB_ThreePartName,len(@Database_B) + 2)))
declare @Schema_B nvarchar(max) = (
    select  replace(replace(@TableB_ThreePartName,@Database_B + '.',''),'.' + @Table_B,''))

-- Get schema for both tables
declare @GetTableADetails nvarchar(max) = '
    use [' + @Database_A +']
        select  COLUMN_NAME
             ,  DATA_TYPE
          from  INFORMATION_SCHEMA.COLUMNS
         where  TABLE_NAME = ''' + @Table_A + '''
           and  TABLE_SCHEMA = ''' + @Schema_A + '''
    '
create table #Table_A_Details (
    ColumnName nvarchar(max)
,   DataType nvarchar(max)
)
insert into #Table_A_Details
exec (@GetTableADetails)

declare @GetTableBDetails nvarchar(max) = '
    use [' + @Database_B +']
        select  COLUMN_NAME
             ,  DATA_TYPE
          from  INFORMATION_SCHEMA.COLUMNS
         where  TABLE_NAME = ''' + @Table_B + '''
           and  TABLE_SCHEMA = ''' + @Schema_B + '''
    '
create table #Table_B_Details (
    ColumnName nvarchar(max)
,   DataType nvarchar(max)
)
insert into #Table_B_Details
exec (@GetTableBDetails)


-- Get differences in table schema
            select  ROW_NUMBER() over (order by
                        a.ColumnName
                    ,   b.ColumnName) as RowKey
                 ,  a.ColumnName as A_ColumnName
                 ,  a.DataType as A_DataType
                 ,  b.ColumnName as B_ColumnName
                 ,  b.DataType as B_DataType
              into  #FieldList
              from  #Table_A_Details a
   full outer join  #Table_B_Details b
                on  a.ColumnName = b.ColumnName
             where  a.ColumnName is null
                or  b.ColumnName is null
                or  a.DataType <> b.DataType

        drop table  #Table_A_Details
        drop table  #Table_B_Details

            select  coalesce(A_ColumnName,B_ColumnName) as ColumnName
                 ,  A_DataType
                 ,  B_DataType
              into  #SchemaVariances
              from  #FieldList

-- Get differences in table data
declare @LastColumn int = (select max(RowKey) from #FieldList)
declare @RowNumber int = 1
declare @ThisField nvarchar(max)
declare @TestSql nvarchar(max)



create table #DataVariances (
    TableKey            nvarchar(max)
,   FieldName           nvarchar(max)
,   TableA_Value        nvarchar(max)
,   TableB_Value        nvarchar(max)
)

delete from #FieldList where A_DataType in ('varbinary','image') or B_DataType in ('varbinary','image') 

while @RowNumber <= @LastColumn begin
    set @TestSql = '
        select  coalesce(a.[' + @KeyName + '],b.[' + @KeyName + ']) as TableKey
             ,  ''' + @ThisField + ''' as FieldName
             ,  a.[' + @ThisField + '] as [TableA_Value]
             ,  b.[' + @ThisField + '] as [TableB_Value]
          from  [' + @Database_A + '].[' + @Schema_A + '].[' + @Table_A + '] a 
    inner join  [' + @Database_B + '].[' + @Schema_B + '].[' + @Table_B + '] b
            on  a.[' + @KeyName + '] = b.[' + @KeyName + ']
         where  ltrim(rtrim(a.[' + @ThisField + '])) <> ltrim(rtrim(b.[' + @ThisField + ']))
            or (a.[' + @ThisField + '] is null and  b.[' + @ThisField + '] is not null)
            or (a.[' + @ThisField + '] is not null and  b.[' + @ThisField + '] is null)
'

insert into #DataVariances
exec (@TestSql)

set @RowNumber = @RowNumber + 1
set @ThisField = (select coalesce(A_ColumnName,B_ColumnName) from #FieldList a where RowKey = @RowNumber)

end

drop table #FieldList

print 'Query complete.  Select from #DataVariances to verify data integrity or #SchemaVariances to verify schemas match.  Data types varbinary and image are not checked.'

Em MySQL, onde "menos" não é suportado, e tendo em conta o desempenho, este é um rápido

query:
SELECT 
t1.id, 
t1.id 
FROM t1 inner join t2 using (id) where concat(t1.C, t1.D, ...)<>concat(t2.C, t2.D, ...)

Uma consulta alternativa melhorada com base na resposta de dietbuddha & IanMc. A consulta inclui uma descrição para mostrar de forma útil onde as linhas existem e estão em falta. (NB: para Servidor SQL)

(
    select 'InTableA_NoMatchInTableB' as Msg, * from tableA
    except
    select 'InTableA_NoMatchInTableB' , * from tableB
)
union all
(
    select 'InTableB_NoMatchInTableA' as Msg, * from tableB
    except
    select 'InTableB_NNoMatchInTableA' ,* from tableA
)

SELECT * 
FROM TABLE A
WHERE NOT EXISTS (SELECT 'X' 
                  FROM  TABLE B 
                  WHERE B.KEYFIELD1 = A.KEYFIELD1 
                  AND   B.KEYFIELD2 = A.KEYFIELD2 
                  AND   B.KEYFIELD3 = A.KEYFIELD3)
;

'X' é qualquer valor.

Certifique-se de se juntar aos campos chave nas suas mesas.

Ou apenas usar o operador menos com 2 declarações selecionadas, no entanto, o MINUS só pode funcionar em Oracle.

SELECT * FROM
   (
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableA
   GROUP BY column1, column2, ...
   MINUS
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableB
   GROUP BY column1, column2, ...
   )
UNION ALL
   (
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableB
   GROUP BY column1, column2, ...
   MINUS
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableA
   GROUP BY column1, column2, ...
   )