如何在 MySQL 中比较两个查询结果是否相等? [英] How to compare two query results for equality in 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.

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.

解决方案

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

这篇关于如何在 MySQL 中比较两个查询结果是否相等?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！