SQL Articles

SQL query to check two tables have identical data


Assume two tables EmpDtl1 and EmpDtl2 have same structure and same number of rows but one row is different in both tables as shown below.

create table EmpDtl1(EmpId int,EmpName varchar(30))

insert into EmpDtl1 values(1,'one')
insert into EmpDtl1 values(2,'two')
insert into EmpDtl1 values(3,'three')
insert into EmpDtl1 values(4,'four')
insert into EmpDtl1 values(5,'five')

create table EmpDtl2(EmpId int,EmpName varchar(30))
insert into EmpDtl2 values(1,'one')
insert into EmpDtl2 values(2,'two')
insert into EmpDtl2 values(3,'three')
insert into EmpDtl2 values(4,'four')
insert into EmpDtl2 values(6,'six')

To cofirm both tables have identical data, Row count returned in below query should be same as number of rows in EmpDtl1, EmpDtl(row count of below query= row count of EmpDtl1= row count of EmpDtl2). Below query returns 6 rows(4 identical rows and 2 different rows) so two tables are not identical.

select count(*) from
(
select * From EmpDtl1
union
select * From EmpDtl2
)T

In case if tables have duplicate rows then, to cofirm both tables have identical data, Row count returned in above query should be same as distinct number of rows in EmpDtl1, EmpDtl2.