CODEWITHSUNDEEP
sqloracle-databaseselect
Vito De Tullio
Vito De Tullio

Reputation: 1685

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

Upvotes: 56

Views: 70954

Answers (5)

Martijn
Martijn

Reputation: 16123

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.

Upvotes: 0

Bogdan
Bogdan

Reputation: 2042

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

Upvotes: 42

Bhuvan
Bhuvan

Reputation: 4187

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.

Upvotes: 13

HAL 9000
HAL 9000

Reputation: 3985

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.

Upvotes: 62

user500944
user500944

Reputation:

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

Upvotes: 0

Related Questions