Check if two "select"s are equivalent

Question

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) results of two selects are the same.

Hyunhz

Do you want to compare the two SELECT strings? or the results? Please provide more Information. – Moxley

'Check if two "select"s are equivalent", where to check it in a Query? stored Procedure? View? Please be some descriptive. – Bounteous

possible duplicate of Proving SQL query equivalency – Bauman

This question is similar to: Proving SQL query equivalency. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Warlock

@jimG. ... have you actually rode the question? – Hyunhz

Answer 1

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.

Boss

If zero rows appear, than it still doesn't mean that the queries are semantically equivalent. It just means that with the current data set they give the same results. This might be good enough for the original poster, though, but I thought it is worth pointing out. – Ozell

@Rob van Wijk you are right, but the tables where the selects works have little (and controlled) modifications, so I can just be "realistically" confident into the new select as a valid "drop in" of the old one – Hyunhz

This doesn't ensure that rows are returned in the same order. – Dreyer

@DorianMarchal Query results are sets, so the order does not matter. The ORDER BY clause does not change the query result, it merely makes it so that the rows are returned in that order. In the absence of an ORDER BY clause, returned order is an implementation detail that each DB is free to choose as it wishes; sometimes, the same query will return in a different order because the query optimizer found a better query plan and starts JOINing from a different table so rows will come in a different order. – Loyola

Answer 2

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

Necropsy

Answer 3

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.

Antrim

Answer 4

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

Norman

"If the result is an empty set then they are equivalent." -- not necessarily, if the second query returns all records from the first plus additional records then you'll still get an empty set (EXCEPT is not commutative). – Guise

Answer 5

For me this did the trick:

Query_1_here

EXCEPT

Query_2_here

Or, a little more real world example:

SELECT 
    BIN_TO_UUID(a.`parent_id`, true) shipmentId, 
    a.name,
    a.company
FROM shipment_address a

EXCEPT

SELECT 
    BIN_TO_UUID(s.id, true) shipmentId, 
    s.name, 
    s.company
FROM shipment s

I got zero results when they are the same. Please note that the names of the columns should be the same in both queries. This might be automatically the same (eg if you time the BIN_TO_UUID out of my example), it might be note and require a simple alias.

Aldridge