如何查询B/C/D/E/F表中a_id不存在于A表的指定格式结果

实现逻辑 #

分别筛选B、C、D、E、F五张表中a_id与A表ID无匹配的记录，给每张表的结果构造对应列的空值占位，最后通过UNION ALL拼接所有结果即可匹配要求的输出格式。

完整查询语句 #

SELECT 
  NULL AS A_ID,
  b.ID AS B_ID,
  NULL AS C_ID,
  NULL AS D_ID,
  NULL AS E_ID,
  NULL AS F_ID
FROM B b
WHERE b.A_ID NOT IN (SELECT ID FROM A)

UNION ALL

SELECT 
  NULL AS A_ID,
  NULL AS B_ID,
  c.ID AS C_ID,
  NULL AS D_ID,
  NULL AS E_ID,
  NULL AS F_ID
FROM C c
WHERE c.A_ID NOT IN (SELECT ID FROM A)

UNION ALL

SELECT 
  NULL AS A_ID,
  NULL AS B_ID,
  NULL AS C_ID,
  d.ID AS D_ID,
  NULL AS E_ID,
  NULL AS F_ID
FROM D d
WHERE d.A_ID NOT IN (SELECT ID FROM A)

UNION ALL

SELECT 
  NULL AS A_ID,
  NULL AS B_ID,
  NULL AS C_ID,
  NULL AS D_ID,
  e.ID AS E_ID,
  NULL AS F_ID
FROM E e
WHERE e.A_ID NOT IN (SELECT ID FROM A)

UNION ALL

SELECT 
  NULL AS A_ID,
  NULL AS B_ID,
  NULL AS C_ID,
  NULL AS D_ID,
  NULL AS E_ID,
  f.ID AS F_ID
FROM F f
WHERE f.A_ID NOT IN (SELECT ID FROM A)

兼容提示 #

如果A表ID字段存在NULL值，为避免NOT IN的逻辑异常，可以把所有过滤条件替换为NOT EXISTS写法，示例如下：

-- 表B的过滤条件替换示例
WHERE NOT EXISTS (SELECT 1 FROM A a WHERE a.ID = b.A_ID)

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