Tips abouts comparing to database tables. Table Diff

Question

Please share your tips about comparing tables :

• showing lines that contain the same values for a given set of columns
• showing lines not contained in table A but contained in table B & vice-versa

Answer 1

I've always been a fan of Redgate tools: SQL Data Compare.

Provides very flexible options to compare tables/database with criteria based on rows, columns, filters, etc. e.g.

It's does for database tables what a diff tool does for text files. Especially useful when trying to identify difference between data constellations between dev and production servers for example. It also has a very handy feature to generate SQL statement based on the delta to apply the changes from one server to a second server.

Answer 2

Regarding the second part of the question:

showing lines not contained in table A but contained in table B & vice-versa

If you're using Oracle, you can use the minus keyword:

select field1, field2, . field_n
from tables
MINUS
select field1, field2, . field_n
from tables;