dood dood - 1 month ago 8
SQL Question

Check if tables are identical using SQL in Oracle

I was asked this question during an interview for a Junior Oracle Developer position, the interviewer admitted it was a tough one:

Write a query/queries to check if the table 'employees_hist' is an exact copy of the table 'employees'. Any ideas how to go about this?

EDIT: Consider that tables can have duplicate records so a simple MINUS will not work in this case.

EXAMPLE

EMPLOYEES

NAME
--------
Jack Crack
Jack Crack
Jill Hill


These two would not be identical.

EMPLOYEES_HIST

NAME
--------
Jack Crack
Jill Hill
Jill Hill

Answer

One possible solution, which caters for duplicates, is to create a subquery which does a UNION on the two tables, and includes the number of duplicates contained within each table by grouping on all the columns. The outer query can then group on all the columns, including the row count column. If the table match, there should be no rows returned:

create table employees (name varchar2(100));
create table employees_hist (name varchar2(100));

insert into employees values ('Jack Crack');
insert into employees values ('Jack Crack');
insert into employees values ('Jill Hill');
insert into employees_hist values ('Jack Crack');
insert into employees_hist values ('Jill Hill');
insert into employees_hist values ('Jill Hill');


with both_tables as
(select name, count(*) as row_count
 from employees
 group by name
union all
 select name, count(*) as row_count
 from employees_hist
 group by name)
select name, row_count from both_tables
group by name, row_count having count(*) <> 2;

gives you:

Name        Row_count
Jack Crack  1
Jack Crack  2
Jill Hill   1
Jill Hill   2

This tells you that both names appear once in one table and twice in the other, and therefore the tables don't match.