I have two tables TableA and TableB which have the same format of columns, which means both tables have the columns
A B C D E F where A and B are the primary keys.
How do I write an SQL query to check if TableA and TableB (which have identical primary keys) contain exactly the same values in every column?
It means that these two tables have exactly the same data.
021 Answers
You should be able to "MINUS" or "EXCEPT" depending on the flavor of SQL used by your DBMS.
select * from tableA minus select * from tableB If the query returns no rows then the data is exactly the same.
8Using relational operators:
SELECT * FROM TableA UNION SELECT * FROM TableB EXCEPT SELECT * FROM TableA INTERSECT SELECT * FROM TableB; Change EXCEPT to MINUS for Oracle.
Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:
- Expressions in parentheses
INTERSECTEXCEPTandUNIONevaluated from left to right.
dietbuddha has a nice answer. In cases where you don't have a MINUS or EXCEPT, one option is to do a union all between the tables, group by with all the columns and make sure there is two of everything:
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); Will return all ID's that are the SAME in both tables. To get the differences change EXISTS to NOT EXISTS.
1Taking the script from onedaywhen, I modified it to also show which table each entry comes from.
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 Enhancement to dietbuddha's answer...
select * from ( select * from tableA minus select * from tableB ) union all select * from ( select * from tableB minus select * from tableA ) Source: Use NATURAL FULL JOIN to compare two tables in SQL by Lukas Eder
Clever approach of using NATURAL FULL JOIN to detect the same/different rows between two tables.
Example 1 - status flag:
SELECT t1.*, t2.*, CASE WHEN t1 IS NULL OR t2 IS NULL THEN 'Not equal' ELSE 'Equal' END FROM t1 NATURAL FULL JOIN t2; Example 2 - filtering rows
SELECT * FROM (SELECT 't1' AS t1, t1.* FROM t1) t1 NATURAL FULL JOIN (SELECT 't2' AS t2, t2.* FROM t2) t2 WHERE t1 IS NULL OR t2 IS NULL -- show differences --WHERE t1 IS NOT NULL AND t2 IS NOT NULL -- show the same just to complet, a proc stored using except method to compare 2 tables and give result in same table with 3 errors status, ADD, DEL, GAP table must have same PK, you declare the 2 tables and fields to compare of 1 or both table
Just use like this ps_TableGap 'tbl1','Tbl2','fld1,fld2,fld3','fld4'fld5'fld6' (optional)
/****** 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 You can find differences of 2 tables using combination of insert all and full outer join in Oracle. In sql you can extract the differences via full outer join but it seems that insert all/first doesnt exist in sql! Hence, you have to use following query instead:
select * from A full outer join B on A.pk=B.pk where A.field1!=B.field1 or A.field2!=B.field2 or A.field3!=B.field3 or A.field4!=B.field4 --and A.Date==Date1 Although using 'OR' in where clause is not recommended and it usually yields in lower performance, you can still use above query if your tables are not massive. If there is any result for the above query, it is exactly the differences of 2 tables based on comparison of fields 1,2,3,4. For improving the query performance, you can filter it by date as well(check the commented part)
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]) Result is null, but sources are different!
But:
( 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]) ) works.
I had this same issue in SQL Server and wrote this T-SQL script to automate the process (actually this is the watered-down version, mine wrote all the diff to a single table for easy reporting).
Update 'MyTable' and 'MyOtherTable' to the names of the tables you wish to compare.
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 I wrote this to compare the results of a pretty nasty view I ported from Oracle to SQL Server. It creates a pair of temp tables, #DataVariances and #SchemaVariances, with differences in (you guessed it) the data in the tables and the schema of the tables themselves.
It requires both tables have a primary key, but you could drop it into tempdb with an identity column if the source tables don't have one.
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.' 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' is any value.
Switch the tables to see the different discrepancies.
Make sure to join the key fields in your tables.
Or just use the MINUS operator with 2 select statements, however, MINUS can only work in Oracle.
1Most of the responses seem to ignore the issue raised by Kamil. (That is where the tables contain identical rows, but different ones are repeated in each table.) Unfortunately, I am not able to use his solution, because I am in Oracle. The best I've been able to come up with is:
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, ... ) To compare T1(PK, A, B) and T2(PK, A, B).
First compare primary key sets to look for missing key values on either side:
SELECT T1.*, T2.* FROM T1 FULL OUTER JOIN T2 ON T1.PK=T2.PK WHERE T1.PK IS NULL OR T2.PK IS NULL; Then list all value mismatch:
SELECT T1.PK, 'A' AS columnName, T1.A AS leftValue, T2.A AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.A,0) != COALESCE(T2.A,0) UNION ALL SELECT T1.PK, 'B' AS columnName, T1.B AS leftValue, T2.B AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.B,0) != COALESCE(T2.B,0) A and B must be of same type. You can use INFORMATION SCHEMA to generate the SELECT. Don't forget the COALESCE to also include IS NULL results. You could also use FULL OUTER JOIN and COALESCE(T1.PK,0)=COALESCE(T2.PK,0).
For example for columns of type varchar:
SELECT concat('SELECT T1.PK, ''', COLUMN_NAME, ''' AS columnName, T1.', COLUMN_NAME, ' AS leftValue, T2.', COLUMN_NAME, ' AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.',COLUMN_NAME, ',0)!=COALESCE(T2.', COLUMN_NAME, ',0)') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='T1' AND DATA_TYPE IN ('nvarchar','varchar'); We can compare data from two tables of DB2 tables using the below simple query,
Step 1:- Select which all columns we need to compare from table (T1) of schema(S)
SELECT T1.col1,T1.col3,T1.col5 from S.T1 Step 2:- Use 'Minus' keyword for comparing 2 tables.
Step 3:- Select which all columns we need to compare from table (T2) of schema(S)
SELECT T2.col1,T2.col3,T2.col5 from S.T1 END result:
SELECT T1.col1,T1.col3,T1.col5 from S.T1 MINUS SELECT T2.col1,T2.col3,T2.col5 from S.T1; If the query returns no rows then the data is exactly the same.
In SQL Server... Using the row counts and then comparing this to the row count of the intersect:
DECLARE @t1count int = (SELECT COUNT(*) FROM table1) IF (@t1count = (SELECT COUNT(*) FROM table2)) IF (SELECT COUNT (*) FROM (SELECT * FROM table1 INTERSECT SELECT * FROM table2) AS dT) = @t1count SELECT 'Equal' ELSE SELECT 'Not equal' ELSE SELECT 'Not equal' I wrote it this way so that when the row counts of the tables aren't equal, then the intersect is completely skipped which will improve performance in those cases.
Try This
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1' intersect SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table2'; select count(a.A) from A a inner join B b on b.A = a.A and b.B = a.B and b.C = a.C and b.D = a.D and b.E = a.E and b.F = a.F if the answer equals with the count of table A that means table A and B are have exactly same data , but if the answer is 0 then the Table A is not equals B . In MySQL, where "minus" is not supported, and taking performance into account, this is a fast
query: SELECT t1.id, t1.id FROM t1 inner join t2 using (id) where concat(t1.C, t1.D, ...)<>concat(t2.C, t2.D, ...) An alternative, enhanced query based on answer by dietbuddha & IanMc. The query includes description to helpfully show where rows exist and are missing. (NB: for SQL Server)
( 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 ) 
