过滤结果时如何确保外键关联行存在且避免两次查询

可以通过CTE（公共表表达式）结合左连接实现，既能验证Owner是否存在，又能获取对应的Pet数据，无需额外发起查询：

核心SQL语句 #

WITH owner_check AS (
    SELECT id FROM owner WHERE id = $1
)
SELECT 
    p.*,
    oc.id IS NOT NULL AS owner_exists
FROM owner_check oc
LEFT JOIN pet p ON oc.id = p.owner_id

代码层处理逻辑 #

1. 执行上述参数化查询（务必用参数代替字符串拼接，避免SQL注入）
2. 根据查询结果分支处理：
   • 结果为空集：目标Owner不存在，抛出OwnerNotFound异常
   • 结果非空，但所有Pet字段（如p.id）为NULL：Owner存在但无关联Pet，返回空列表
   • 结果包含非空Pet数据：提取所有Pet字段（忽略owner_exists标记）返回

替代方案（无需CTE） #

如果不想用CTE，也可以用UNION ALL组合查询实现相同逻辑：

SELECT 
    p.*,
    TRUE AS owner_exists
FROM pet p
WHERE owner_id = $1
UNION ALL
SELECT 
    NULL::int AS id,  -- 按Pet表字段类型依次用NULL填充
    NULL::int AS owner_id,
    -- 其他Pet字段需对应类型的NULL值
    TRUE AS owner_exists
WHERE EXISTS(SELECT 1 FROM owner WHERE id = $1) 
  AND NOT EXISTS(SELECT 1 FROM pet WHERE owner_id = $1)

该方案中，Owner存在但无Pet时会返回一行全NULL的Pet数据（带owner_exists=true），Owner不存在时返回空集，判断逻辑与前一种方案一致。

关键注意点 #

• 禁止直接拼接SQL字符串，必须使用参数化查询防范SQL注入
• 处理结果时要匹配Pet表的字段类型，确保NULL值判断准确

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