如何在MySQL中比较两个查询结果的相等性？

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.

我是MySQL用户。我有两个问题，我希望比较他们的平等结果。我想用一个返回true或false的查询来执行此操作，因此我的两个查询中的每一个都很可能采用子查询的形式。

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!

我已经搜索了一些关于如何做到这一点的例子，但是空手而归。一些示例代码将非常受欢迎，因为我是SQL编程的新手。谢谢！

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.

注意：我正在寻找一个适用于任意两个任意查询的解决方案，所以我将不再发布我的两个查询恰好是什么。

3 个解决方案

#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

#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

如果MySQL支持FULL OUTER JOIN，这会更容易一些注意，如果这两个查询给出相同的结果，但顺序不同，则会被认为是对等的

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

如果count = 0，那么两个查询是相同的

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

另外因为我正在使用UNION重复项被删除。所以那里存在潜在的不准确性。

#3

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

你不能在MySQL中做MINUS，所以这里是如何做到的：

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

#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

#2