如何测试两段查询未读消息的SQL语句是否等价？

首先明确：这两段SQL逻辑完全等价，都是用来查询channel为'U560'且未被标记为已读（即read_messages表中无对应记录）的消息ID和频道信息。

测试等价性可以用以下几种方法：

• 直接对比查询结果
  在目标数据库中分别执行两段SQL，将结果导出后对比行数和每条数据的内容。也可以用数据库的集合运算直接验证，比如用EXCEPT（或部分数据库的MINUS）检查两段结果是否无差异：

  -- 检查第一段结果减去第二段结果是否为空
  SELECT messages1.id, messages1.channel 
  FROM (SELECT * FROM messages WHERE channel IN ('U560')) messages1 
  LEFT JOIN read_messages ON messages1.id = read_messages.message_id 
  WHERE read_messages.id IS NULL
  EXCEPT
  SELECT "messages"."id", "messages"."channel" 
  FROM "messages" 
  LEFT OUTER JOIN "read_messages" ON ("messages"."id" = "read_messages"."message_id") 
  WHERE ("messages"."channel" IN ('U560') AND "read_messages"."id" IS NULL);
  

  再反过来执行一次（第二段结果减第一段），如果两次查询都返回空结果，说明两段SQL的输出完全一致。

• 查看执行计划
  用数据库的执行计划工具分析两段SQL的执行逻辑：

  • MySQL：在SQL前加EXPLAIN
  • PostgreSQL：用EXPLAIN ANALYZE
  • SQL Server：执行SET SHOWPLAN_XML ON后运行SQL
    如果两段SQL的执行计划核心步骤（比如表访问方式、连接类型、过滤条件的执行顺序、扫描行数）一致，说明数据库对它们的优化逻辑相同，进一步验证等价性。

• 边界场景测试
  构造特殊数据场景验证两段SQL的表现是否一致：

  • 场景1：messages表中没有channel为'U560'的记录，验证两段SQL是否都返回空结果；
  • 场景2：某条'U560'频道的消息在read_messages中有对应记录，验证两段SQL是否都不返回这条数据；
  • 场景3：某条'U560'频道的消息在read_messages中无对应记录，验证两段SQL是否都返回这条数据；
  • 场景4：read_messages表为空，验证两段SQL是否都返回所有'U560'频道的消息。

内容的提问来源于stack exchange，提问作者Leonardo Furtado