PostgreSQL如何合并多条SELECT为单条查询并处理关联空值问题

你需要把所有INNER JOIN替换为LEFT JOIN即可实现需求，LEFT JOIN会保留左表（也就是你主查询的links实例表）的所有符合条件的记录，即便右表没有匹配的关联数据，对应字段会返回NULL而不是过滤掉整条记录。

注意需要把原来写在WHERE里的各关联表过滤条件（比如c1.name = 'name'、s1.name = 'value'这类）移到对应LEFT JOIN的ON条件中，否则NULL值记录还是会被WHERE过滤，等效于INNER JOIN的效果。

修改后的SQL示例：

select 
  "x"."id" as "id", 
  "s1"."value" as "name", 
  "s2"."value" as "inc_id", 
  "s3"."value" as "website", 
  "s4"."value" as "revenue", 
  "s5"."value" as "verified" 
from "links" as "x" 
left join "links" as "c1" on "c1"."parent_id" = "x"."id" and "c1"."name" = 'name'
left join "string_links" as "s1" on "s1"."parent_id" = "c1"."value" and "s1"."name" = 'value'
left join "links" as "c2" on "c2"."parent_id" = "x"."id" and "c2"."name" = 'inc_id'
left join "string_links" as "s2" on "s2"."parent_id" = "c2"."value" and "s2"."name" = 'value'
left join "links" as "c3" on "c3"."parent_id" = "x"."id" and "c3"."name" = 'website'
left join "text_links" as "s3" on "s3"."parent_id" = "c3"."value" and "s3"."name" = 'value'
left join "links" as "c4" on "c4"."parent_id" = "x"."id" and "c4"."name" = 'revenue'
left join "integer_links" as "s4" on "s4"."parent_id" = "c4"."value" and "s4"."name" = 'value'
left join "boolean_links" as "s5" on "s5"."parent_id" = "x"."id" and "s5"."name" = 'verified'
where "x"."parent_id" = '47c1956bz31330' 
and "x"."name" = 'link'

如果你的数据库支持横向子查询（PostgreSQL、MySQL 8.0+、Oracle均支持），可以用LATERAL JOIN简化写法，更适合动态生成字段逻辑，每个字段的逻辑独立，维护成本更低：

select 
  x.id,
  name_val.value as name,
  inc_id_val.value as inc_id,
  website_val.value as website,
  revenue_val.value as revenue,
  verified_val.value as verified
from links x
left join lateral (
  select sl.value
  from links c
  join string_links sl on sl.parent_id = c.value and sl.name = 'value'
  where c.parent_id = x.id and c.name = 'name'
  limit 1
) name_val on true
left join lateral (
  select sl.value
  from links c
  join string_links sl on sl.parent_id = c.value and sl.name = 'value'
  where c.parent_id = x.id and c.name = 'inc_id'
  limit 1
) inc_id_val on true
-- 其他字段以此类推
left join lateral (
  select bl.value
  from boolean_links bl
  where bl.parent_id = x.id and bl.name = 'verified'
  limit 1
) verified_val on true
where x.parent_id = '47c1956bz31330' and x.name = 'link'

批量查询100条实例的场景可直接复用上述逻辑，只需修改顶层x表的WHERE条件，支持分页、筛选等操作，不需要调整字段关联部分。

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