如果id是pk，您可以使用下面的查询找到您的解决方案。

select id,
(case when id in (select * from tableA)  then 'YES' else 'NO' end) as PRESENTINA,
(case when id not in (select * from tableB)  then 'NO' else 'YES' end) as PRESENTINB
from tableA
union
select id,
(case when id in (select * from tableA)  then 'YES' else 'NO' end) As PRESENTINA,
(case when id not in (select * from tableB)  then 'NO' else 'YES' end) as PRESENTINB 
from tableB

如果 id 是独一无二的，你可以用 full join :

select id,
       (case when a.id is not null then 'Yes' else 'No' end) as PresentInA,
       (case when b.id is not null then 'Yes' else 'No' end) as PresentInB
from a full join
     b
     using (id);

如果 id 不是唯一的，可以向上面添加聚合。相反，我想我应该用 union all 以及 group by :

select id, max(PresentInA) as PresentInA, max(PresentInB) as PresentInB
from ((select distinct a.id, 'Yes' as PresentInA, 'No' as PresentInB
       from a
      ) union all
      (select distinct b.id, 'No' as PresentInA, 'Yes' as PresentInB
       from b
      ) 
     ) ab
group by id;

这可以防止过度处理 id 在两个表中重复多次。

使用并集和左连接你尝试如下

with cte
as(
select 1 as id
union all
select 2
union all
select 3 )
,
cte2 AS
(
select 2 as id
union all
select 3 
union all
select 4 )
, cte3 as (
select id from cte
union 
select id from cte2
) select cte3.id, case when cte.id is null then 'NO' else 'Yes' end as prestnInA,
case when cte2.id is null then 'NO' else 'Yes' end as prestnInB
    from cte3
  left join cte on cte3.id=cte.id
  left join cte2 on cte3.id=cte2.id

输出

id  prestnInA   prestnInB
1   Yes         NO
2   Yes         Yes
3   Yes         Yes
4   NO          Yes

演示链接