SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables.  You don't need to worry about NULLS, the code is fairly short and easy to follow, and you can view exceptions from both tables at the same time.

Well, now in SQL 2005, we have another option:  using EXCEPT and INTERSECT.  And these are even easier!

To return all rows in table1 that do not match exactly the rows in table2, we can just use EXCEPT like this:

select * from table1 except select * from table2

To return all rows in table2 that do not match exactly in table1, we reverse the EXCEPT:

select * from table2 except select * from table1

And to return all rows in table1 that match exactly what is in table2, we can use INTERSECT:

select * from table1 intersect select * from table2

In all of the above examples, the columns must match between the two tables, of course.

Thus, we can return a listing of all rows from either table that do not match completely by using UNION ALL to combine the results of both EXCEPT queries:

select  'table1' as tblName,  *  from
  (select * from table1 
   except 
   
select * from table2) x

union all
select  'table2' as tblName,  *  from
  (select * from table2 
   except 
select *  
   from table1) x


And we can now write a very simple stored procedure that compares any two tables (assuming the schemas match, of course) like this:

create procedure CompareTables @table1 varchar(100), @table2 varchar(100)
as
  declare @sql varchar(8000)
  set @sql = 'select ''' + @table1 + ''' as tblName, *  from
      (select * from ' + @table1 + '
       except
       select * from ' + @table2 + ') x
    union all
    select ''' + @table2 + ''' as tblName, *  from
      (select * from ' + @table2 + '
       except 
       select *
 from ' + @table1 +') x'

  exec(@sql)

Of course, both tables must have primary keys in place; duplicate values in these tables will not make logical sense when trying to determine which rows match or not.

So, EXCEPT and INTERSECT are pretty handy.  Does anyone else have any suggestions for ideas where these operators can make things shorter, quicker or more efficient compared to older (pre SQL 2005) methods?
 

转载于:https://www.cnblogs.com/yehuabin/archive/2012/01/02/2310284.html

内容概要:该题库专为研究生入学考试计算机组成原理科目设计,涵盖名校考研真题、经典教材课后习题、章节题库和模拟试题四大核心模块。名校考研真题精选多所知名高校的计算机组成原理科目及计算机联考真题,并提供详尽解析,帮助考生把握考研命题趋势与难度。经典教材课后习题包括白中英《计算机组成原理》(第5版)和唐朔飞《计算机组成原理》(第2版)的全部课后习题解答,这两部教材被众多名校列为考研指定参考书目。章节题库精选代表性考题,注重基础知识与重难点内容,帮助考生全面掌握考试大纲要求的知识点。模拟试题依据历年考研真题命题规律和热门考点,精心编制两套全真模拟试题,并附标准答案,帮助考生检验学习成果,评估应试能力。 适用人群:计划参加研究生入学考试并报考计算机组成原理科目的考生,尤其是需要系统复习和强化训练的学生。 使用场景及目标:①通过研读名校考研真题,考生可以准确把握考研命题趋势与难度,有效评估复习成效;②通过经典教材课后习题的练习,考生可以巩固基础知识,掌握解题技巧;③通过章节题库的系统练习,考生可以全面掌握考试大纲要求的各个知识点,为备考打下坚实基础;④通过模拟试题的测试,考生可以检验学习成果,评估应试能力,为正式考试做好充分准备。 其他说明:该题库不仅提供详细的题目解析,还涵盖了计算机组成原理的各个方面,包括计算机系统概述、数据表示与运算、存储器分层、指令系统、中央处理器、总线系统和输入输出系统等。考生在使用过程中应结合理论学习与实践操作,注重理解与应用,以提高应试能力和专业知识水平。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值