Check if two "select"s are equivalent

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-equivalency stop me.

For my actual need I can just check if the (actual) resu开发者_JS百科lts of two selects are the same.

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.

In standard SQL you can write following

(select * from query1 EXCEPT select * from query2) 
UNION ALL
(select * from query2 EXCEPT select * from query1)

I wanted to note that MINUS is not standard SQL so we need to use EXCEPT instead

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.

[1] Minus is not supported,so need to use EXCEPT as told by @Bogdan

[2] Using only EXCEPT does not consider duplicates so had to use EXCEPT ALL

[3] EXCEPT ALL require that column order in the resultant should be same so in above query QUERY1 and QUERY2 should 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)

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.

will update if i come across more edge case which may fail.

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.

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.

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