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.

If you provide us with what you've already got, someone might be able to help you. — Jack Murdoch, Jul 08 '11 at 15:55
I have nothing. Like I said in the post, I have come up empty handed — Gwen Avery, Jul 08 '11 at 15:56
Perhaps provide your database schema and what data you're looking to retrieve - there may be a better alternative. — Jack Murdoch, Jul 08 '11 at 15:57
Are you limited to `MySQL`? This would be trivial in `SQL Server` — JNK, Jul 08 '11 at 16:01
One trivial solution might be to use `EXPLAIN` and compare the query plans. don't know if that applies to your particular situation. — SingleNegationElimination, Jul 08 '11 at 16:04
@TokenMacGuy: EXPLAIN produces the EXPLAIN plan; the OP wants to diff the result set — OMG Ponies, Jul 08 '11 at 16:06
@JNK: Yep, yet another case of ANSI syntax (`EXCEPT`) that MySQL needs to implement :/ Oracle's had `MINUS` for a while... — OMG Ponies, Jul 08 '11 at 16:07

score 21 · Answer 1 · answered Jul 08 '11 at 16:08

21

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

answered Jul 08 '11 at 16:08

MatBailie

83,401
18
103
137

+1 nice and simple. Of course it has the same problems as mine with dups and order – Conrad Frix Jul 08 '11 at 16:19
perhaps if you went with UNION ALL and did `WHEN Count1 = count2 AND (Count1 *2) = Count3` you wouldn't have the Dupe problem – Conrad Frix Jul 08 '11 at 16:30
1

The logic *only* works because of UNION not including duplicates. Otherwise it is only checking the relative number of records, not the contents. – MatBailie Jul 09 '11 at 09:34
@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. – tonix Jan 26 '16 at 14:48
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? – Urasquirrel Nov 10 '20 at 03:46
@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). – MatBailie Nov 10 '20 at 16:08

score 4 · Answer 2 · answered Jul 08 '11 at 16:03

4

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.

answered Jul 08 '11 at 16:03

Conrad Frix

51,984
12
96
155

Could the inaccuracy be solved by using UNION ALL instead of UNION to preserve the duplicates? – Gwen Avery Jul 08 '11 at 16:17
This isn't truly universal though since you need to alter the `JOIN` and `WHERE` clauses every time the inner queries change. – JNK Jul 08 '11 at 16:20
@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 Jul 08 '11 at 16:28
@Conrad - sorry to be unclear, just noticed OP asked for something as generic as possible. – JNK Jul 08 '11 at 17:16

score 4 · Answer 3 · answered Jul 08 '11 at 16:15

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 );

How to compare two query results for equality in MySQL?

3 Answers3

Linked