Comparing values of 2 tables and listing the rows that are different

Question

This question is in the same vein as this question, but just slightly different.

I have 2 tables, TableA and TableB where TableB is really just a backup of TableA, the only real difference is that TableB has an "ACTION" column that indicates the action that had been taken in TableA. For now I am ignoring that column (although ideally I would want it appended to the final results, so please feel free to suggest on that too :D )

I want to compare the two tables and get the rows from each table that are different. I've used Jeff's SQL Server Blog - The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION ! example to create a query, however, it returns only the rows from TableB that are different.

What is the recommended approach?

Note: the code is exactly like the example in the second link, so I won't post any of it here

Answer 1

This will give you everything in A that's not in B

select * from tableA
Except
select * from tableB

and vice-versa

select * from tableB
Except
select * from tableA

Edit: Joined this way:

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