如何基于两个相似SQL查询合并为一个优化的查询语句？

方案一：使用CTE统一实体数据源 #

这种方式通过CTE将organizations和persons的必要字段合并为一个统一的实体视图，后续只需编写一次关联逻辑，避免重复代码：

WITH unified_entities AS (
    -- 组织实体：直接取name字段
    SELECT 
        id,
        name,
        "entityType"
    FROM organizations
    
    UNION ALL
    
    -- 个人实体：拼接姓名作为匹配用的name
    SELECT 
        id,
        -- 处理NULL情况，避免拼接后得到NULL导致匹配失败
        COALESCE("firstName", '') || ' ' || COALESCE("lastName", '') AS name,
        "entityType"
    FROM persons
)
SELECT cias.*
FROM catalog_investors_advanced_search AS cias
INNER JOIN unified_entities e
    ON e.name = cias.name
    AND e."entityType" = cias."entityType"
INNER JOIN investments AS i 
    ON i."entityId" = e.id 
    AND i."entityType" = e."entityType"
ORDER BY i."createdAt" DESC;

优势：

• 代码结构清晰，逻辑复用性强
• 仅需一次关联investments表，减少重复扫描
• 易于扩展（后续新增实体类型只需在CTE中添加分支）

方案二：使用LATERAL JOIN精准匹配实体 #

如果希望根据cias."entityType"的值仅扫描对应的数据表（避免不必要的表访问），可以用LATERAL JOIN实现：

SELECT cias.*
FROM catalog_investors_advanced_search AS cias
INNER JOIN investments i ON TRUE
INNER JOIN LATERAL (
    -- 匹配投资机构类型
    SELECT o.id, o."entityType"
    FROM organizations o
    WHERE cias."entityType" = 'investment-firm'
      AND o.name = cias.name
      AND i."entityId" = o.id 
      AND i."entityType" = o."entityType"
    
    UNION ALL
    
    -- 匹配个人投资者类型
    SELECT p.id, p."entityType"
    FROM persons p
    WHERE cias."entityType" = 'investor'
      AND COALESCE(p."firstName", '') || ' ' || COALESCE(p."lastName", '') = cias.name
      AND i."entityId" = p.id 
      AND i."entityType" = p."entityType"
) AS matched_entity ON TRUE
ORDER BY i."createdAt" DESC;

优势：

• 仅当cias."entityType"匹配时才扫描对应的表，减少无效IO
• 关联逻辑更精准，适合数据量较大的场景

注意事项： #

1. 处理个人姓名拼接时，用COALESCE避免因firstName或lastName为NULL导致拼接结果为NULL，进而无法匹配cias.name
2. 确保organizations.name、persons.firstName/lastName以及cias.name的字符编码和格式一致（比如空格、大小写），否则会出现匹配失败
3. 可以根据实际执行计划选择最优方案，若数据库支持CTE物化，方案一的性能会更稳定；若数据量差异大，方案二的针对性扫描会更高效

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