如何证明2条sql语句等价 [英] how to prove 2 sql statements are equivalent

问题描述

我开始用连接和子语句重写一个复杂的 SQL 语句，并获得了一个看起来更简单的语句.我通过在相同的数据集上运行两者并获得相同的结果集来测试它.一般来说，我如何(从概念上)证明这 2 个语句在任何给定数据集中是相同的?

I set out to rewrite a complex SQL statement with joins and sub-statements and obtained a more simple looking statement. I tested it by running both on the same data set and getting the same result set. In general, how can I (conceptually) prove that the 2 statements are the same in any given data set?

推荐答案

我建议学习关系代数(正如 Mchl 所指出的).如果您想认真地优化查询和正确设计数据库，这是您需要的最基本的概念.

I would suggest studying relational algebra (as pointed out by Mchl). It is the most essential concept you need if you want to get serious about optimizing queries and designing databases properly.

但是，如果您有足够的数据进行测试，我会建议一种丑陋的蛮力方法，它可以帮助您确保正确的结果:创建两个版本的视图(使比较更易于管理)并比较结果.我的意思是像

However I will suggest an ugly brute force approach that helps you to ensure correct results if you have sufficient data to test with: create views of both versions (for making the comparisons easier to manage) and compare the results. I mean something like

create view original as select xxx yyy zzz;
create view new as select xxx yyy zzz;
-- If the amount differs something is quite obviously very wrong
select count(*) from original;
select count(*) from new;
-- What is missing from the new one?
select *
from original o
where not exists (
 select * 
 from new n
 where o.col1=n.col2 and o.col2=n.col2 --and so on
);
-- Did something extra appear?
select *
from new o
where not exists (
 select *
 from old n
 where o.col1=n.col2 and o.col2=n.col2 --and so on
)

另外，正如其他人在评论中指出的那样，您可能会将这两个查询提供给您正在使用的产品的优化器.大多数时候你会得到一些可以被人类解析的东西，完整的执行路径图以及子查询对性能的影响等等.它最常见的是像

Also as pointed out by others in comments you might feed both the queries to the optimizers of the product you are working with. Most of the time you get something that can be parsed with humans, complete drawings of the execution paths with the subqueries' impact on the performance etc. It is most often done with something like

explain plan for 
select * 
from ...
where ...
etc

这篇关于如何证明2条sql语句等价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！