Snippets: SQL: Comparing two tables

 29th July 2021 at 5:11pm

给定两个 schema 一样的表,如何判断它们的数据是否相同?

假定有两张表 t1t2,它们的主键都是 col1。下面的语句可以把它们之中有区别的行查询出来:

SELECT * FROM t1 FULL OUTER JOIN t2
    ON  COALESCE(t1.col1, 0)=COALESCE(t2.col1, 0)
    AND COALESCE(t1.col2, 0)=COALESCE(t2.col2, 0)
    AND COALESCE(t1.col3, 0)=COALESCE(t2.col3, 0)
    --- ... 所有其他字段
WHERE
(
       t1.col1 IS NULL        --- 这表示仅出现在 t2 中的行
    OR t2.col1 IS NULL        --- 这表示仅出现在 t1 中的行
)

重点在于:

  • FULL OUTER JOIN:见 SQL: Queries: Table Expressions: Join
  • 要进行比较的字段都应该在 JOIN 条件中
  • 对于可能为 NULL 的字段,应该使用 COALESCE() 函数;因为 NULL = NULL 的取值为 false
    • 当然这可能导致 NULL 与默认值(如 0)被认为是相等的

如果你期望缩小对比的数据范围,比如:

t1.grass_date=date'2021-07-20' AND t2.grass_date=date'2021-07-20';

那你不能直接在 WHERE 语句中添加。因为如果 t1 中有某行在 t2 中不存在,那么 FULL OUTER JOIN 的结果是,t1 的全部列有值,但 t2 的全部列为 NULL。如果你在 WHERE 中写上 t2.grass_date=date'2021-07-20' 条件,那么这些列会被过滤掉,也就达不到想要的目的。正确的做法应该是使用 CTEwith 语句):

WITH nt1 AS (
  SELECT * FROM t1 WHERE t1.grass_date=date'2021-07-20'
), nt2 AS (
  SELECT * FROM t2 WHERE t2.grass_date=date'2021-07-20'
)
SELECT * FROM nt1 FULL OUTER JOIN nt2
    ON  COALESCE(nt1.col1, 0)=COALESCE(nt2.col1, 0)
    AND COALESCE(nt1.col2, 0)=COALESCE(nt2.col2, 0)
    AND COALESCE(nt1.col3, 0)=COALESCE(nt2.col3, 0)
    --- ... 所有其他字段
WHERE
(
       nt1.col1 IS NULL        --- 这表示仅出现在 t2 中的行
    OR nt2.col1 IS NULL        --- 这表示仅出现在 t1 中的行
)