比较两张表数据是否一致,直接用inner join就可以了吗?

发表于:2020-3-10 09:31

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:陈霞    来源:51Testing软件测试网原创

分享:
   在批量程序的测试中,经常会涉及到对数据库表的测试,今天我们来介绍一下用sql比较两张表结构相同的表数据是否完全一致的方法。
  1、inner join 浅尝
  提到比对两张表的数据是否完全相同,很容易想到用inner join关联两张表去比较。Inner join的基本语法是:
  select collist from t1 inner join t2 on condition1 and condition2...;
  简单理解就是对于t1表中的每一行,将它与t2表的每一行进行比较,检查他们是否满足关联条件,当满足关联条件时,inner join 将返回由t1和t2表中的列组成的新行,当无法满足关联条件时,将返回一个空结果。下面举个例子:
   create table t1(id number,name varchar2(10));
  insert into t1 values(1, 'A');
  insert into t1 values(2, 'B');
  insert into t1 values(3, 'C');
  insert into t1 values(4, 'D');
  select * from t1;
  得到t1表数据如下

   create table t2(id number,name varchar2(10));
  insert into t2 values(1, 'A');
  insert into t2 values(2, 'B');
  insert into t2 values(3, 'C');
  insert into t2 values(4, 'D');
  select * from t2;
  得到t2表数据如下
  t1和t2表结构相同,用inner join做两张表关联,条件是相对应的字段都完全一致。sql语句和结果如下:
 select * from t1 inner join t2 on t1.ID=t2.ID and t1.NAME=t2.NAME;


select count(*) from t1 inner join t2 on t1.ID=t2.ID and t1.NAME=t2.NAME;
  
   由上述例子可见,用inner join比较两张表结构完全相同的表,当关联条件为对应字段都相等时,若t1表的条数=t2表的条数=两张表inner join的返回条数,则说明这两张表数据内容是完全一致的。
  2、小心数据重复陷阱
  以上是用inner join语句时最容易想到的思路,但是我们忽略了一点,inner join原理是对两张表做乘积操作,对于表内有重复数据时候,就会出现漏洞,举例如下:
  假设t1表数据如下:
  t2表数据如下:
  用inner join对两张表做关联操作,条件是相对应字段值都相等,语句和执行结果如下:
 select * from t1 inner join t2 on t1.ID=t2.ID and t1.NAME=t2.NAME;
  

 select count(*) from t1 inner join t2 on t1.ID=t2.ID and t1.NAME=t2.NAME;
  
   由此可见,当所对比的两张表内有重复数据时,用最初的inner join 方法直接关联两张表判断数据是否完全一致将会出现问题。
  3、一种简单的改进方法
  如何解决呢?一种简单的改进方法就是对两张表分别按整行分组,并计算分组的条数,得到t1’和t2’,此时的t1’和t2’是不存在任何重复数据的两张表,再对t1’和t2’用最初的方法通过inner join关联比较。下面举例说明:
  对t1分组并求条数,得到t1’,语句和执行结果如下:
 select id,name,count(1) as num from t1 group by id,name;

  对t2分组并求条数,得到t2’,语句和执行结果如下:
 select id,name,count(1) as num from t2 group by id,name;

  对t1’和t2’用inner join语句做关联和比较,语句和执行结果如下:
   select *
  from (select id,name,count(1) as num from t1 group by id,name)  table1
  inner join (select id,name,count(1) as num from t2 group by id,name)  table2 on table1.ID=table2.ID
  and table1.name=table2.name
  and table1.num=table2.num;
  
   若t1’的条数=t2’的条数=t1’和t2’inner join关联(条件是对应字段值都相等)返回条数,则说明t1和t2表数据完全相同。在上述例子中,可以看到改进后的inner join语句可以判断出t1 和 t2表数据并不完全一致。
  4、更多判断语句
  其实除了inner join语句外,还有很多种方法可以比较两张表的数据是否完全一致。
  如用minus 对两张表做减法,返回t1表减去t2表后的行,返回t2表减去t1表后的行,判断是否返回的内容都为空
   select * from t1 minus select * from t2;
  select * from t2 minus select * from t1;
   如用exists对表中的每一行逐一判断,返回在t1中存在但t2中不存在的行,返回在t2中存但在t1中不存在的行,查看是否返回的内容都为空:
   select * from t1
  where not exists(select * from t2 where  t1.ID=t2.ID  and t1.NAME=t2.NAME);
  select * from t2
  where not exists(select * from t1 where  t1.ID=t2.ID  and t1.NAME=t2.NAME);
  如将t1和t2中的所有字段以字符串的形式拼接起来,判断t1拼接后的字符串是否都在t2中存在,t2拼接后的字符串是否都在t1中存在:
   select * from t1 where t1.ID||t1.NAME not in (select t2.ID||t2.NAME from t2);
  select * from t2 where t2.ID||t2.NAME not in (select t1.ID||t1.NAME from t1);
  值得注意的是,以上三种方法同样也不适用于有重复数据时的情况。但是没关系,通过前文inner join举例,我们已经学会了采用对整行分组求条数得到t1’和t2’的方法,通过对t1’和t2’进行比较,同样可以获得比对t1和t2数据是否完全一致的正确结果。

      版权声明:本文出自51Testing会员投稿,51Testing软件测试网及相关内容提供者拥有内容的全部版权,未经明确的书面许可,任何人或单位不得对本网站内容复制、转载或进行镜像,否则将追究法律责任。
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号