Set operators可以用来找出两个表之间的差异.
EXCEPT ALL
从第一个结果集中的第二个结果集中删除相同的行,这必须以两种顺序运行才能获得所有差异.UNION ALL
将两个结果集合并在一起.如果返回任何行,则它们是不同的.COUNT(*) = 0
将把它作为布尔值返回.
-- Example data and table names with a CTE, this isn't required.
WITH TABLE1 (var1, var2) AS (
VALUES (1, 'a'), (1,'b'), (1,'c')
),
TABLE2 (var1, var2) AS (
VALUES (1, 'a'), (1,'b'), (1,'c')
)
-- Compare these tables
SELECT COUNT(*) = 0 AS IS_IDENTICAL FROM
(
(
SELECT * FROM TABLE1
EXCEPT ALL
SELECT * FROM TABLE2
)
UNION ALL
(
SELECT * FROM TABLE2
EXCEPT ALL
SELECT * FROM TABLE1
)
)
;
输出: