Oracle minus keyword

Oracle’s MINUS is what most other databases call EXCEPT — it returns rows from the first query that don’t appear in the second. I use it most often for data reconciliation: finding records that exist in a new table but are missing from the old one. The cleanup section is there because I always forget to drop temp tables.

• Create tables

create table CLIENT_TABLE_OLD(
    CLIENTID number(19)
);

create table CLIENT_TABLE_NEW(
    CLIENTID number(19) primary key
);

• insert data into tables

insert into CLIENT_TABLE_NEW (CLIENTID) values(1);
insert into CLIENT_TABLE_NEW (CLIENTID) values(2);
insert into CLIENT_TABLE_NEW (CLIENTID) values(3);
insert into CLIENT_TABLE_NEW (CLIENTID) values(4);
insert into CLIENT_TABLE_NEW (CLIENTID) values(5);

insert into CLIENT_TABLE_OLD (CLIENTID) values(4);
insert into CLIENT_TABLE_OLD (CLIENTID) values(5);
insert into CLIENT_TABLE_OLD (CLIENTID) values(6);
insert into CLIENT_TABLE_OLD (CLIENTID) values(7);
insert into CLIENT_TABLE_OLD (CLIENTID) values(8);

• Find all records that are missing from old table

select clientid from CLIENT_TABLE_NEW
minus
select clientid from CLIENT_TABLE_OLD;

CLIENTID
1
2
3

Cleanup

• Drop tables

drop table CLIENT_TABLE_OLD;
drop table CLIENT_TABLE_NEW;