证明SQL查询的等效性 [英] Proving SQL query equivalency

问题描述

您将如何证明两个查询在功能上是等效的，例如它们将始终都返回相同的结果集.

How would you go about proving that two queries are functionally equivalent, eg they will always both return the same result set.

由于我在执行此操作时会想到一个特定的查询，所以最终按照@dougman的建议进行操作，在有关表的约10％的行中进行比较并比较结果，以确保没有不合适的结果.

As I had a specific query in mind when I was doing this, I ended up doing as @dougman suggested, over about 10% of rows the tables concerned and comparing the results, ensuring there was no out of place results.

推荐答案

您能做的最好的事情是根据给定的一组输入比较两个查询输出，以查找任何差异.要说它们对于所有输入总是返回相同的结果，实际上取决于数据.

The best you can do is compare the 2 query outputs based on a given set of inputs looking for any differences. To say that they will always return the same results for all inputs really depends on the data.

对于Oracle，最好的方法之一(不是最有效的方法)(非常有效)在这里( Ctrl + F 比较两个表的内容):

For Oracle one of the better if not best approaches (very efficient) is here (Ctrl+F Comparing the Contents of Two Tables):
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

归结为:

select c1,c2,c3, 
       count(src1) CNT1, 
       count(src2) CNT2
  from (select a.*, 
               1 src1, 
               to_number(null) src2 
          from a
        union all
        select b.*, 
               to_number(null) src1, 
               2 src2 
          from b
       )
group by c1,c2,c3
having count(src1) <> count(src2);

这篇关于证明SQL查询的等效性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！