检查两个表在PostgreSQL中是否具有相同的内容

#1 楼

一种选择是以以下形式在两个表之间使用FULL OUTER JOIN：

SELECT count (1)
    FROM table_a a
    FULL OUTER JOIN table_b b 
        USING (<list of columns to compare>)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

例如：

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (3, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

将返回2的计数，而：

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (2, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

返回希望计数为0。

与这种方法类似的是，与使用EXISTS时，只需要读取一次每个表两次。此外，这对支持完全外部联接的数据库（不仅仅是Postgresql）也适用。

我通常不鼓励使用USING子句，但是在一种情况下，我认为它是更好的方法。

附录2019-05-03：

如果存在可能为空数据的问题（即id列不可为空但val是），则可以尝试以下操作：

SELECT count (1)
    FROM a
    FULL OUTER JOIN b
        ON ( a.id = b.id
            AND a.val IS NOT DISTINCT FROM b.val )
    WHERE a.id IS NULL
        OR b.id IS NULL ;

#2 楼

您可以使用EXCEPT运算符。例如，如果表具有相同的结构，则以下内容将返回一个表中的所有行，而不返回另一表中的所有行（如果表具有相同的数据，则返回0行）：


或使用EXISTS返回布尔值或带有两个可能结果之一的字符串：

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

在SQLfiddle上测试/>
还不是EXCEPT会删除重复项（如果您的表具有PRIMARY KEY或UNIQUE约束，那不应该担心，但是如果您正在比较可能产生重复行的任意查询的结果，则可能是这样。） br />
EXCEPT关键字所做的另一件事是将NULL值视为相同，因此，如果表A的行包含(1,2,NULL)，而表B的行包含(1,2,NULL)，则第一个查询将不显示这些行，如果两个表都没有其他行，则第二个查询将返回'same'。

如果要计算这样的行与s不同，您可以对gsiems的FULL JOIN答案使用变体，以获取所有（不同的）行：

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

，并获得是/否的答案：

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL 
   OR b.some_not_null_column IS NULL ;

如果两个表的所有列都不为空，则两种方法将给出相同的答案。

#3 楼

您需要Except子句
类似

SELECT * FROM first_table
EXCEPT
SELECT * FROM second_table

，这将从第一张表返回所有不在第二张表中的行

#4 楼

使用except是查找表1中而不是表2中的内容（反之亦然）并将结果并集的关键。如果它们相同，则计数为零。

;with CTE1 AS
(
  (
    select column1, column2 from Table1
    except
    select column1, column2 from Table2
  )
  UNION
  (
    select column1, column2 from Table2
    except
    select column1, column2 from Table1
  )
)
Select count(*) From CTE1  -- If identical count should be 0.

#5 楼

查看链接的代码，您将无法理解：

select count(*) from
(
select * From EmpDtl1
union
select * From EmpDtl2
)

秘密的调味品是使用union而不是union all。前者仅保留不同的行，而后者则保留重复的行（参考）。换句话说，嵌套查询说“给我EmpDtl1中的所有行和列，以及EmpDtl1中尚未存在的所有行和列”。当且仅当EmpDtl2不对结果贡献任何行（即两个表相同）时，此子查询的计数才等于EmpDtl1的计数。或者，按键顺序将表转储到两个文本文件，并使用您选择的比较工具。