Oracle union keyword

A quick thing worth remembering: UNION removes duplicates, UNION ALL doesn’t — and UNION ALL is significantly faster because it skips the deduplication step. If you know your datasets don’t overlap, use UNION ALL. If you’re not sure, UNION is the safe default.

• 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 unique records that are present in both the tables

select clientid from CLIENT_TABLE_NEW
union
select clientid from CLIENT_TABLE_OLD;

CLIENTID
1
2
3
4
5
6
7
8

Cleanup

• Drop tables

drop table CLIENT_TABLE_OLD;
drop table CLIENT_TABLE_NEW;