Find if two tables are containing identical data

sql snippets

SELECT 1 FROM (
   SELECT * FROM table1
   UNION ALL
   SELECT * FROM table2
) t
GROUP BY col1, col2, col3
HAVING count(*) = 1
LIMIT 1

List all the columns in GROUP BY to compare the entire table.
If the result is an empty set, the two tables are identical.


to see which rows differ:
SELECT * FROM (
   SELECT 'DAL_ObjectHistory_View' tname,Status, Id, InstanceId, CDTName, Namespace, CDTNameWithNamespace, OldValue, OldValueReadMore, NewValue, NewValueReadMore,
parentVariableName, instanceVariableId, ProcessModelId, ModifiedBy, ModifiedDateTime  FROM DAL_ObjectHistory_View
   UNION ALL
   SELECT 'DAL_ObjectHistory_View2' tname, Status, Id, InstanceId, CDTName, Namespace, CDTNameWithNamespace, OldValue, OldValueReadMore, NewValue, NewValueReadMore,
parentVariableName, instanceVariableId, ProcessModelId, ModifiedBy, ModifiedDateTime FROM DAL_ObjectHistory_View2
) t
GROUP BY Status, Id, InstanceId, CDTName, Namespace, CDTNameWithNamespace, OldValue, OldValueReadMore, NewValue, NewValueReadMore,
parentVariableName, instanceVariableId, ProcessModelId, ModifiedBy, ModifiedDateTime
HAVING count(*)=1;