Easy way to compare the contents of 2 tables ?

SQL Apprentice Question
I've got a couple tables with identical structure...

I would like to create an exception report indicating any differences in the
content of any of the columns (even in the case where one value may be null
and its counterpart a space)... Any ideas on how to build such a query ?

Celko Answers
>> I've got a couple tables with identical structure...<<

That should not happen. A table should contain all the entities of the
same kind in one and only one table.


>> I would like to create an exception report indicating any differences in the content of any of the columns (even in the case where one value may be null and its counterpart a space)... Any ideas on how to build such a query ? <<


If you have SQL-2005

(SELECT * FROM Foo
EXCEPT
SELECT * FROM Bar)
UNION
(SELECT * FROM Bar
EXCEPT
SELECT * FROM Foo)


This does not require that you know the structure of the tables, just
that they are alike.
This is called a OUTER UNION (not the same as an OUTER JOIN!) and is
defined in the SQL-92 Standards. Nobody implements it.