SQL 检查两个“选择”是否等效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5727882/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if two "select"s are equivalent
提问by Vito De Tullio
Is there a way to check if two (non-trivial) select are equivalent?
有没有办法检查两个（非平凡的）选择是否等效？
Initially I was hoping for a formally equivalence between two selects, but the answers in proving-sql-query-equivalencystop me.
最初我希望两个选择之间有形式上的等价，但proving-sql-query-equivalency 中的答案 阻止了我。
For my actual need I can just check if the (actual) results of two selects are the same.
对于我的实际需要，我可以检查两个选择的（实际）结果是否相同。
回答by HAL 9000
If you want to compare the query results try the following:
如果要比较查询结果，请尝试以下操作：
(select * from query1 MINUS select * from query2)
UNION ALL
(select * from query2 MINUS select * from query1)
This will result in all rows that are returned by only one of the queries.
这将导致仅由一个查询返回的所有行。
回答by Bogdan
Was not able to comment on the answer given by HAL9000 and I wanted to note that MINUS is not standard SQL and it doesn't work under postgresql So we need to use EXCEPT instead
无法对 HAL9000 给出的答案发表评论，我想指出 MINUS 不是标准 SQL，它在 postgresql 下不起作用所以我们需要改用 EXCEPT
(select * from query1 EXCEPT select * from query2)
UNION ALL
(select * from query2 EXCEPT select * from query1)
回答by Bhuvan
For
为了
(select * from query1 EXCEPT select * from query2)
UNION ALL
(select * from query2 EXCEPT select * from query1)
I did some trial on postgres 9.4, and here are my results.
我对 进行了一些试验postgres 9.4，这是我的结果。
[1] Minus is not supported,so need to use EXCEPTas told by @Bogdan
[1] 不支持减号，因此需要EXCEPT按照@Bogdan 的说明使用
[2] Using only EXCEPTdoes not consider duplicates so had to use EXCEPT ALL
[2] Using onlyEXCEPT不考虑重复，所以必须使用EXCEPT ALL
[3] EXCEPT ALLrequire that column order in the resultant should be same so in
above query QUERY1and QUERY2should either return the same column order or we have to wrap the query and make sure the column order are same.(may be this happens in application logic)
[3]EXCEPT ALL要求结果中的列顺序在上面的查询中应该相同，QUERY1并且QUERY2应该返回相同的列顺序，或者我们必须包装查询并确保列顺序相同。（这可能发生在应用程序逻辑中）
So i think if we keep above 3 points in mind we may be 100% sure that the data returned by two queries on the given data set is exactly the same.
所以我认为，如果我们牢记以上 3 点，我们可以 100% 确定对给定数据集的两个查询返回的数据完全相同。
will update if i come across more edge case which may fail.
如果我遇到更多可能失败的边缘情况，我会更新。
回答by Bhuvan
Run both of them and compare the results. Use the EXCEPT operation to subtract the set returned by the first query from the set returned by the second query. If the result is an empty set then they are equivalent.
运行它们并比较结果。使用 EXCEPT 操作从第二个查询返回的集合中减去第一个查询返回的集合。如果结果是一个空集，那么它们是等价的。
The problem with this method is that it does not prove that two queries are equivalent for ANY database. It depends on the content of your database. For example, if your DB is empty, then any two select statements are equivalent according to this method.
这种方法的问题在于它不能证明两个查询对于任何数据库都是等价的。这取决于您的数据库的内容。例如，如果您的数据库为空，那么根据此方法，任何两个 select 语句都是等效的。
Proving equivalence by just analyzing the queries is an unsolved problem AFAIK (but I'm not exactly a database theory guru, so dont trust me on that ;)) Also, you can have a look at this question: Proving SQL query equivalency
仅通过分析查询来证明等效性是一个未解决的问题 AFAIK（但我不完全是数据库理论专家，所以不要相信我；））此外，您可以看看这个问题：Proving SQL query equivalency
