Compare Two Tables in SQL

You may want to compare the results processed while you develop and enhance data processing tasks. For example, you might want to see if the table from improved query are the same as the one of the existing query. If the tables are different, you might want to know how many rows are different and which field is different. In other words, you might want to compare two tables, such as the Linux diff command.

You might hesitate to compare two tables row by row if you feel the table is so big. But, don’t worry. You can make the cluster of servers compare the tables by simple SQL statement.

Set operators in SQL

You can use the SQL set operator to compare tables. For SQL set operators, the elements of a set are the rows. You may know the UNION operator which is frequently used to concatenate tables with the same schema. The SQL statement A UNION B returns the set union of the set A and B. Since the union operator is in SQL, it is natural that there are intersection and difference set operators in SQL. The former is INTERSECT and the latter is EXCEPT. The Venn diagram of SQL set operators are depicted as follows:

EXCEPT operator

We can get the difference between the two tables using the EXCEPT operator. Note that A EXCEPT B returns only the rows that are in A but not in B. This statement does not return rows that are in A but not in B. Rows that are in A but not in B can be obtained with the statement ‘B EXCEPT A’. That is, you must combine the result of EXCEPT B with the result of B EXCEPT A.

As a result, you can get the difference between the two tables in the same way as the following SQL statement:

(
    SELECT "A" AS TBL, *
    FROM (
        (SELECT * FROM A)
        EXCEPT
        (SELECT * FROM B)
    )
) UNION (
    SELECT "B" AS TBL, *
    FROM (
        (SELECT * FROM B)
        EXCEPT
        (SELECT * FROM A)
    )
)