Mastering Oracle SQL

Using Set Operations to Compare Two Tables

Developers, and even DBAs, occasionally need to compare the contents of two tables to determine whether the tables contain the same data. The need to do this is especially common in test environments, as developers may want to compare a set of data generated by a program under test with a set of “known good” data. Comparison of tables is also useful for automated testing purposes, when we have to compare actual results with a given set of expected results. SQL’s set operations provide an interesting solution to this problem of comparing two tables.

The following query uses both MINUS and UNION ALL to compare two tables for equality. The query depends on each table having either a primary key or at least one unique index.

(SELECT * FROM CUSTOMER_KNOWN_GOODMINUS
SELECT * FROM CUSTOMER_TEST)UNION ALL
(SELECT * FROM CUSTOMER_TESTMINUS
SELECT * FROM CUSTOMER_KNOWN_GOOD);

Let’s talk a bit about how this query works. We can look at it as the union of two compound queries. The parentheses ensure that both MINUS operations take place first before the UNION ALL operation is performed. The result of the first MINUS query will be those rows in CUSTOMER_KNOWN_GOOD that are not also in CUSTOMER_TEST. The result of the second MINUS query will be those rows in CUSTOMER_TEST that are not also in CUSTOMER_KNOWN_GOOD. The UNION ALL operator simply combines these two result sets for convenience. If no rows are returned by this query, then we know that ...