Verification of records like finding the difference between two tables can be easily done in the database. Using the NOT IN keyword, the records that are in one table, but are not included in the other table, can be determined.

EXAMPLE DATABASE: Training2

DATA INPUT

The inputs are tables from the Training2.mdb database namely, asia and eastern_asia. The aim is to identify the countries that are in one table, but are not included in the other table.

The asia table contains the country code, name and population of some countries in Asia.

While the eastern_asia table contains the country code, name and population of the countries in East Asia.

QUERY country_missing_in_asia

SELECT cnt_code, cnt_name, cnt_pop, "eastern_asia" AS source
FROM eastern_asia
WHERE cnt_code NOT IN (SELECT cnt_code FROM asia);

The country_missing_in_asia query lists the East Asian countries which are not included in the Asia table.

DISCUSSION

The query requires the country code, name, population and the source table. The phrase "eastern_asia" AS source in the SELECT statement is an added column stating the source table, which is eastern_asia. To identify the East Asian countries that are not included in the Asia table, it is declared in the WHERE statement that the country code from eastern_asia table must be NOT IN the list of country codes in the asia table.

With this simple SQL statement, there was already a comparison made between the two tables, plus the output of the difference between them.

QUERY country_differences

SELECT cnt_code, cnt_name, cnt_pop, "eastern_asia" AS source
FROM eastern_asia
WHERE cnt_code NOT IN (SELECT cnt_code FROM asia);

UNION ALL

SELECT cnt_code, cnt_name, cnt_pop, "asia" AS source
FROM asia
WHERE cnt_code NOT IN (SELECT cnt_code FROM eastern_asia);

The country_differences query lists the East Asian countries which are not included in the Asia table, and also, the list of Asian countries which are not in the Eastern Asia table.

DISCUSSION

The first SELECT statement does the same as the previous query which retrieves the East Asian countries that are not included in the asia table. The UNION ALL statement combined head-to-tail the countries that appear in the asia table, but are not included in the eastern_asia table.