Menu
How would you go about proving that two queries are functionally equivalent, eg they will always both return the same result set.
As I had a specific query in mind when I was doing this, I ended up doing as @dougman suggested, over about 10% of rows the tables concerned and comparing the results, ensuring there was no out of place results.
874
In SQL, you can use the >= operator to test for an expression greater than or equal to. Let's use the same customers table as the previous example.
Two SQL queries are semantically equivalent if they produce the same results given any valid input relations. Here is an example of two semantically equivalent SQL queries: These two queries will produce the exact same results no matter what the input relations r and s contain, and no matter what the predicate p is.
Comparing the Results of the Two Queries The solution to this is very simple. Run both queries using a UNION to combine the results! The UNION operator returns unique records. If the two results sets are identical the row count will remain the same as the original query.
The equivalence rule says that expressions of two forms are the same or equivalent because both expressions produce the same outputs on any legal database instance.
The best you can do is compare the 2 query outputs based on a given set of inputs looking for any differences. To say that they will always return the same results for all inputs really depends on the data.
For Oracle one of the better if not best approaches (very efficient) is here (Ctrl+F Comparing the Contents of Two Tables):
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html
Which boils down to:
select c1,c2,c3, count(src1) CNT1, count(src2) CNT2 from (select a.*, 1 src1, to_number(null) src2 from a union all select b.*, to_number(null) src1, 2 src2 from b ) group by c1,c2,c3 having count(src1) <> count(src2); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
