In Oracle
Using MINUS and UNION ALL one can compare 2 tables to find differences in data in the tables.

SELECT 'Table1', a.* FROM
(SELECT * FROM table1 MINUS SELECT * FROM table2) a
UNION ALL
SELECT 'Table2', a.* FROM
(SELECT * FROM table2 MINUS SELECT * FROM table1) b;

Example:

SQL> select * from test1;

X
———-
10

SQL> select * from test2;

no rows selected

SQL> SELECT 'Table1', a.* FROM
(SELECT * FROM test1 MINUS SELECT * FROM test2) a
UNION ALL
SELECT 'Table2', b.* FROM
(SELECT * FROM test2 MINUS SELECT * FROM test1) b;

‘TABLE X
—— ———-
Table1 10

In SQL Server 2K
Example:
x
—
1
1

SELECT * FROM testtbl2
x
—
1
1
2

SELECT * FROM
(SELECT x, count(1) cnt FROM twOLTP.dbo.testtbl1 group by x UNION ALL SELECT x, count(1) cnt FROM twOLTP.dbo.testtbl2 group by x) A
GROUP BY a.x, cnt
HAVING COUNT(*) = 1

x cnt
— —
2 1

In SQLServer 2005 – In SQLServer 2005, it has EXCEPT which is similar to MINUS so using that one can compare data in 2 tables.

select 'table1' as tblName, * from
(select * from testtbl1
except
select * from testtbl2) x
union all
select #39;table2' as tblName, * from
(select * from testtbl2
except select *
from testtbl1) x

tblName x
——- ———–
table2 2

• General DBA
• SQL Server