Is there a quick way to compare two equally formatted tables in SQL?

Question

I would like to make an SQL query to compare two tables with the same columns, names and types. Each table has a unique key. I want the query to return any rows containing unequal values. I know can do something like this

select * from table_1, table_2 where table_1.key = table_2.key and ( table_1.col1 != table_2.col1 OR table_1.col2 != table_2.col2 OR ... ) 

but that would be tedious since there are a large and potentially variable number of columns.

change

If this helps, I use the tsql system.

Answer 1

Not sure which type of database you are using, but if you are using SQL Server 2005 or higher, try the following:

 select 'table1' as tblName, * from (select * from table1 except select * from table2) x union all select 'table2' as tblName, * from (select * from table2 except select * from table1) x 

Answer 2

How does this happen...

 select * from table1 where not exists (select * from table2) union all select * from table2 where not exists (select * from table1) 

Answer 3

Checked using SQL Server:

 (select * from table1 except select * from table2) union (select * from table2 except select * from table1); 

Tested using Oracle:

 (select * from table1 minus (select * from table2)) union (select * from table2 minus (select * from table1))