How to verify if two tables have exactly the same data?

You can just use CHECKSUM TABLE and compare the results. You can even alter the table to enable live checksums so that they are continuously available.

CHECKSUM TABLE original_table, backup_table;

It doesn't require the tables to have a primary key.

I would write three queries.

1. An inner join to pick up the rows where the primary key exists in both tables, but there is a difference in the value of one or more of the other columns. This would pick up changed rows in original.

2. A left outer join to pick up the rows that are in the original tables, but not in the backup table (i.e. a row in original has a primary key that does not exist in backup). This would return rows inserted into the original.

3. A right outer join to pick up the rows in backup which no longer exist in the original. This would return rows that have been deleted from the original.

You could union the three queries together to return a single result set. If you did this you would need to add a column to indicate what type of row it is (updated, inserted or deleted).

With a bit of effort, you might be able to do this in one query using a full outer join. Be careful with outer joins, as they behave differently in different SQL engines. Predicates put in the where clause, instead of the join clause can sometimes turn your outer join into an inner join.

select count(*) 
from lemmas as original_table 
      full join backup_table using (lemma_id)
where backup_table.lemma_id is null
      or original_table.lemma_id is null
      or original_table.lemma != backup_table.lemma

The full join / check for null should cover additions or deletions as well as changes.

• backup.id is null = addition
• original.id is null = deletion
• neither null = change

1: First get count for both the tables C1 and C2. C1 and C2 should be equal. C1 and C2 can be obtained from the following query

 select count(*) from table1

if C1 and C2 are not equal, then the tables are not identical.

2: Find distinct count for both the tables DC1 and DC2. DC1 and DC2 should be equal. Number of distinct records can be found using the following query:

select count(*) from (select distinct * from table1)

if DC1 and DC2 are not equal, the tables are not identical.

3: Now get the number of records obtained by performing a union on the 2 tables. Let it be U. Use the following query to get the number of records in a union of 2 tables:

 SELECT count (*)
 FROM 
    (SELECT *
    FROM table1
    UNION
    SELECT *
    FROM table2)

You can say that the data in the 2 tables is identical if distinct count for the 2 tables is equal to the number of records obtained by performing union of the 2 tables. ie DC1 = U and DC2 = U

However, You can also use the 'Except' keyword in SQL Server.

SELECT Col1, Col2, Col3, Col4, Col5 FROM Table_1
EXCEPT
SELECT Col1, Col2, Col3, Col4, Col5 FROM Table_2

If the query returns any rows: They are not Identical.

If the query returns no rows: They are Identical.

Most DBMSs support relational algebra's DIFFERENCE operation. In Snowflake, it is represented as MINUS. So the following code would work. The first MINUS makes sure every element in Table1 is also in Table2. The second MINUS makes sure every element in Table2 is also in Table1. If the query returns a zero, mathematically, the two tables are exactly the same.

select count(*) from 
(
 (
  SELECT * FROM Table1
  MINUS
  SELECT * FROM Table2
 )
 UNION ALL
 (
  SELECT * FROM Table2
  MINUS
  SELECT * FROM Table1
 )
)

Easy workaround for the quick gain:

SELECT sum(col1), sum(col2) FROM table1
UNION
SELECT sum(col1), sum(col2) FROM table2

Output is like:

and then check for differences by testing around:

SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0
UNION
SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0

or by group+sum() and then subtract one table's aggregation from the other table's aggregation and order by the difference.

More details, on the PostgreSQL case which is the same for this example, see Checking whether two tables have identical content in PostgreSQL.