Clever Geek Handbook

Check if tables are identical using SQL in Oracle

I was asked this question during an interview for the Junior Developer Developer position, the interviewer admitted that it was difficult:

Write query / queries to check if the "employee_hist" table is an exact copy of the "employees" table. Any ideas how to do this?

EDIT: Consider that tables might have duplicate records, so a simple MINUS won't work in this case.

Example

EMPLOYEES

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

The two will not be identical.

EMPLOYEES_HIST

NAME
--------
Jack Crack
Jill Hill
Jill Hill
+3


source to share


7 replies


One possible solution that works for duplicates is to create a subquery that performs a UNION on the two tables and includes the number of duplicates contained in each table by grouping by all columns. The outer query can be grouped on all columns, including the row count column. If the table matches, no rows should be 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, so the tables are not the same.

+1


source


Identical for what? Metadata or actual table data too?

Use anyway MINUS

.

select * from table_1

MINUS

Select * from table_2

`

So, if the two tables are indeed identical, i.e. metadata and actual data, they won't return any rows. Otherwise, it will prove that the data is different.



If you get an error, it would mean that the metadata itself is different.

Refresh If the data does not match and that one of the tables has duplicates.

Just select unique records from one table and just apply MINUS

to another table.

+1


source


You can concatenate two tables and then subtract one of the tables from the result. If the result of the subtraction is an empty table, then you know the tables must be the same since the merge had no effect (every row and column was actually the same)

How to combine two tables with different column numbers while removing duplicates?

This link provides a nice way to join two tables without duplicates without knowing what the columns are.

0


source


Use row_number to make sure there are no duplicate rows. Now you can use minus and if there are no results, the tables are identical.

SELECT ROW_NUMBER() OVER (Order By Name), *
FROM tab1
MINUS
SELECT ROW_NUMBER() OVER (Order By Name), *
FROM tab2
0


source


If the tables have the same columns, you can use this; this will not return rows if the rows in both tables are identical:

(
select * from test_data_01
minus
select * from test_data_02
)
union
(
select * from test_data_02
minus
select * from test_data_01
);
0


source 


select name, count(*) n from EMPLOYEES group by name
minus
select name, count(*) n from EMPLOYEES_HIST group by name
union all ( 
select name, count(*) n from EMPLOYEES_HIST group by name
minus
select name, count(*) n from EMPLOYEES group by name)
0


source


Make sure the rows are unique by adding a pseudo column

WITH t1 AS
  (SELECT <All_Columns>
        , row_number() OVER
            (PARTITION BY <All_Columns>
             ORDER BY <All_Columns>) row_num
   FROM employees)
, t2 AS
  (SELECT <All_Columns>
        , row_number() OVER
            (PARTITION BY <All_Columns>
             ORDER BY <All_Columns>) row_num
   FROM employees_hist)
(SELECT *
 FROM t1
 MINUS
 SELECT *
 FROM t2
UNION ALL
(SELECT *
 FROM t1
 MINUS
 SELECT *
 FROM t2)
0


source






More articles:


All Articles