How to compare two query results for equality in MySQL?

Question

I'm a MySQL user. I have two queries, and I wish to compare their results for equality. I would like to do this with a single query which would return true or false, so each of my two queries would most likely take the form of sub-queries.

I would like to avoid returning the results from both queries and comparing them at the application level, to cut down on communication and to improve performance. I would also like to avoid looping over the results at the database level if possile, but if there's no other way, so be it.

I have searched high and low for an example on how to do this, but have come up empty handed. Some sample code would be most appreciated, because I'm a newbie to SQL programming. Thanks!

Note: I'm looking for a solution which would work with any two arbitrary queries, so I'm going to refrain from posting what my two queries happen to be.

Solution 1

SELECT
  CASE WHEN count1 = count2 AND count1 = count3 THEN 'identical' ELSE 'mis-matched' END
FROM
(
  SELECT
    (SELECT COUNT(*) FROM <query1>) AS count1,
    (SELECT COUNT(*) FROM <query2>) AS count2,
    (SELECT COUNT(*) FROM (SELECT * FROM query1 UNION SELECT * FROM query2) AS unioned) AS count3
)
  AS counts

Solution 2

This would be a little easier if MySQL supported FULL OUTER JOIN also note that if the the two queries give the same results but in different order that will be deemed equivlant

SELECT 
  COUNT(*) 
FROM 
(
    (SELECT A, b, c FROM A) a
    LEFT OUTER JOIN 
      (SELECT A, b, c FROM b) B
    ON A.a = b.a and a.b = b.b and a.c = b.c
UNION 
    (SELECT A, b, c FROM A) a
   RIGHT OUTER JOIN 
   (SELECT A, b, c FROM b) B
    ON A.a = b.a and a.b = b.b and a.c = b.c
) 
WHERE a.a is null or b.a is null

If the count =0 then the two queries are the same

Also because I'm using UNION duplicates are being removed. So there's a potential inaccuracy there.

Solution 3

You can't do MINUS in MySQL, so here's how to do it without:

select if(count(*)=0,'same','different') from (
select col1,col2,col3
from tableone
where ( col1, col2, col3 ) not in
( select col4, col5, col6
  from tabletwo )
union
select col4, col5, col6
from tabletwo
where ( col4, col5, col6 ) not in
( select col1, col2, col3
  from tableone )
) minusintersec;

That's given:

tableone (col1 integer, col2 integer, col3 integer );

tabletwo (col4 integer, col5 integer, col6 integer );

Comments

[Gwen Avery]

Could the inaccuracy be solved by using UNION ALL instead of UNION to preserve the duplicates?

[Conrad Frix]

+1 nice and simple. Of course it has the same problems as mine with dups and order

[JNK]

This isn't truly universal though since you need to alter the JOIN and WHERE clauses every time the inner queries change.

[Conrad Frix]

@JNK hmm so the comment is "the query will change every time the query changes" good to know ;) I suppose Dems solution requires fewer changes but its no more universal.

[Conrad Frix]

perhaps if you went with UNION ALL and did WHEN Count1 = count2 AND (Count1 *2) = Count3 you wouldn't have the Dupe problem

[JNK]

@Conrad - sorry to be unclear, just noticed OP asked for something as generic as possible.

[MatBailie]

The logic only works because of UNION not including duplicates. Otherwise it is only checking the relative number of records, not the contents.

[tonix]

@MatBailie Does this tell me that both query 1 and query 2 return me the same result set (I mean the same number of records where each record of query1 has a same record of query 2 and the fields of the record has the same value)? Of course, the order is not taken into consideration here.

[Urasquirrel]

This works well when the query is relatively simple and there aren't a ton of mixed joins, but how can someone be sure that some columns aren't now null, where there were values before?

[MatBailie]

@Urasquirrel - The UNION in count3 takes care as that. A value changing from NULL to NOT NULL (in one row) would cause the UNION to have one more row than the two independent sets (count1 and count2).