Using a full outer join to “diff” two tables

I was inspired to write this post by Joshua Ottwell’s thoughts on finding rows present in one table but not another.

What follows is an exploration of how we can use a Full Outer Join to perform a “diff” on the data in two tables. We’ll also look at doing something similar for two distinct result sets from the same table.

In this instance, we want to identify :

• records that exist in the first table but not the second
• records that exist in the second table but not the first
• records that exist in both tables but where some values differ

Before going any further, I should say that the example that follows will make more sense if you consider Terry Pratchett’s observation that :

“In ancient times cats were worshipped as gods; they have not forgotten this.”

You’ll need an offering if you want to use this computer !

The Family Firm

Let’s say I have a report that I want to change.
I’ve simulated the output of the report and captured it in a table like this :

create table employee_report_baseline( 
    id number,
    emp_name varchar2(100),
    job_title varchar2(100) )
/

insert into employee_report_baseline( id, emp_name, job_title)
values(1,'DEBBIE', 'CEO');

insert into employee_report_baseline( id, emp_name, job_title)
values(2, 'MIKE', 'MINION');

insert into employee_report_baseline( id, emp_name, job_title)
values(3, 'TEDDY', 'DOG');

commit;

The output of the new version of the report is simulated like this :

create table employee_report_new 
    as 
    select * 
    from employee_report_baseline 
    where emp_name != 'MIKE';

update employee_report_new
set job_title = 'LADY-IN-WAITING'
where emp_name = 'DEBBIE';

insert into employee_report_new( id, emp_name, job_title)
values(4, 'CLEO', 'CAT');

commit;

Identifying missing or new records

First of all, let’s see if the records returned are the same for both reports. Happily ID is a unique key for the dataset, which makes the comparison fairly simple :

The absence of the key value from either table indicates that the record is in one table but not the other.

Sure enough, when we run this query we get :

BASELINE_ID NEW_ID STATUS                        
----------- ------ ------------------------------
          2        Missing from New Report       
                 4 Added in New Report           

All Differences

If we want to identify all of the differences, including changed records, we can do so with just a little extra typing :

As well as the two records that are missing from either result set, we can see that Debbie has been demoted ( or possibly usurped). Cleo is now the top dog(!) :

Comparing table definitions

One thing to bear in mind when using a full outer join is that it will match any row in the tables being joined.
This gets a bit annoying when you want to do something with a subset of data such as comparing the column definitions of two tables in USER_TAB_COLUMNS in Oracle.

To demonstrate :

create table old_firm(
    id number,
    first_name varchar2(500),
    last_name varchar2(500),
    job_title varchar2(100),
    start_date date,
    end_date date)
/

create table new_firm(
    id number,
    first_name varchar2(250),
    last_name varchar2(500),
    salary number,
    start_date date,
    end_date varchar2(20))
/

If we want to compare only the records relating to these two tables then we’ll need a couple of in-line-views to restrict the result sets that our full outer join will look at.
Incidentally, as we’re running this on Oracle 19c, we can throw in an in-line function as well to save a bit of typing…

I’ll have to leave it there. The “boss” is demanding Dreamies to appease her wrath for being referred to as “top dog” earlier.