PostgreSQL：如何排查连接两侧缺失表及实现合并查询

需求说明 #

需要完成两项检测：

• 特定Schema（Schema_X）中存在，但未在required_tables表内的表
• required_tables表中存在，但未在Schema_X内的表

已解决的第一个查询（Schema_X中的多余表） #

你已经通过右连接实现了第一个需求，正确的查询语句如下：

select t1.table_name as t1_table_name, t2.table_name as t2_extra_tables_in_schema 
from required_tables t1 
right join information_schema.tables t2 
on t1.table_name = t2.table_name 
where t2.table_schema='Schema_X' 
and t1.table_name IS NULL 

修复第二个查询（必填表中缺失的表） #

你之前的左连接查询出现问题，是因为把t2.table_schema='Schema_X'放在了WHERE子句里——左连接后，当required_tables中的表在Schema_X里不存在时，t2的所有字段都会是NULL，WHERE条件会直接过滤掉这些行，导致结果为空。

正确的做法是将Schema过滤条件移到JOIN的ON子句中，这样只会匹配Schema_X里的表，不匹配的行保留t1的数据且t2为NULL：

select t1.table_name as t1_tables_missing_from_schema, t2.table_name
from required_tables t1 
left join information_schema.tables t2 
on t1.table_name = t2.table_name 
and t2.table_schema='Schema_X'  -- 把Schema条件移到这里
where t2.table_name IS NULL

单查询同时获取两种缺失结果（全外连接方案） #

可以用全外连接（FULL OUTER JOIN）一次性得到两种缺失情况，还可以新增一个字段标记缺失类型，方便区分：

select 
    case 
        when t1.table_name is null then 'Schema_X中多余的表'
        when t2.table_name is null then 'required_tables中缺失的表'
    end as missing_type,
    coalesce(t1.table_name, t2.table_name) as table_name
from required_tables t1 
full outer join information_schema.tables t2 
on t1.table_name = t2.table_name 
and t2.table_schema='Schema_X'
where t1.table_name is null or t2.table_name is null

这个查询会返回所有两种情况的记录，missing_type字段明确说明是哪种缺失，table_name则显示对应的表名。

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