Table Diff

Compare two tables Data!

You are asked to compare two tables and list the difference. You need a master and a copy-table to work with. Let's define what master and copy tables are:

Master_Table: This is the table that contains original data and you are comparing your copy-table against it. This is the source of truth and the data does not changes on this table.

Copy-Table: This is a copy of Master table but later there are some change (update/delete/insert) made to the data.

Now, you are asked to find the difference between these tables meaning, which records are missing or updated on Copy Table.
 Let's do a fun exercise to demo the differences!

--Drop table master_table;

CREATE TABLE master_table
  (
    ID    NUMBER,
    Fname VARCHAR2(25),
    Lname VARCHAR2(25)
  );

--Verify the structure
DESC master_table;

--Insert Data to Master Table
BEGIN
INSERT INTO master_table (ID, fname, lname) VALUES ( 1,'John','Smith');
INSERT INTO master_table (ID, fname, lname) VALUES ( 2,'Howard','Johnson');
INSERT INTO master_table (ID, fname, lname) VALUES ( 3,'Jeremy','Lamar');
INSERT INTO master_table (ID, fname, lname) VALUES ( 4,'Big','Ale');
INSERT INTO master_table (ID, fname, lname) VALUES ( 5,'Miss','Willis');
commit;
END;
/
--Verify the inserts
SELECT * FROM master_table;

--Creating a Copy Table modelled after Maste_table along with Data
-- Lets create using - Create table AS (CTAS)

CREATE TABLE copy_table AS
  ( SELECT id, fname, lname FROM master_table
  );

--verify the copy_table
select * from copy_table;

--Let's compare these two table as it is....
SELECT * FROM
  ( SELECT * FROM master_table
  MINUS
  SELECT * FROM copy_table
  );

--Returns nothing cause there's no change made.

--Lets modify copy_table.

BEGIN
UPDATE copy_table SET fname ='Billy' WHERE ID = 5;
commit;
END;
/
--Verify the change
select * from copy_table where id=5;

-- Lets compare these two tables
SELECT * FROM
  ( SELECT * FROM master_table
  MINUS
  SELECT * FROM copy_table
  );

ID FNAME                     LNAME                
-- ------------------------- -------------------------
 5 Miss                      Willis                  

--So what's this is telling you is? Record id 5 is changed

--Lets modify more on  the copy table
BEGIN
delete from copy_table WHERE ID = 1;
commit;
END;
/
--Lets run the table diff script!
SELECT * FROM
  ( SELECT * FROM master_table
  MINUS
  SELECT * FROM copy_table
  );

ID FNAME                     LNAME                
-- ------------------------- -------------------------
 1 John                      Smith                  
 5 Miss                      Willis

So, far we modified two records from the copy table.The script is displaying the correct diff.
Let's modify one more time...

BEGIN
--Updating id =2 from Howard to howard
UPDATE copy_table SET fname ='howard' WHERE ID =2;
commit;
END;
/
--Let's see if the difference script will pick the case change?
SELECT * FROM
  ( SELECT * FROM master_table
  MINUS
  SELECT * FROM copy_table
  );

ID FNAME                     LNAME                
-- ------------------------- -------------------------
 1 John                          Smith                  
 2 Howard                    Johnson                
 5 Miss                          Willis  

Boom, it does work...
I will be happy to work on any scenarios that you may have and does not work. Hope, you find this writing useful.