在深层视图中编写视图和嵌套视图时,我有时会遗漏某些内容并最终丢失行/数据.如何检查两个不同表中的列是否具有完全匹配的数据？
例：
select count(distinct table1.col1)
from table1
where table1.col1 not in (select distinct table2.col1
from table2);
这将返回table1.col1中不在table2中的值的数量.但是,我不知道这是一个很好的解决方案,因为它不计算table1.col1中不存在的table2.col1值.
解决方法
使用：
SELECT MAX(x.t1_missing_count) AS t1_missing_count,MAX(x.t2_missing_count) AS t2_missing_count
FROM (
SELECT COUNT(DISTINCT t1.col1) AS t1_missing_count,NULL AS t2_missing_count
FROM TABLE_1 t1
WHERE t1.col1 NOT IN (SELECT DISTINCT t2.col1
FROM TABLE_2 t2)
UNION ALL
SELECT NULL,COUNT(DISTINCT t2.col1),FROM TABLE_2 t2
WHERE t2.col1 NOT IN (SELECT DISTINCT t1.col1
FROM TABLE_1 t1)) x
