SQL条件连接：键列含NULL值时的多维度表关联方案咨询

方案1：用UNION ALL拆分两种场景（兼容性最好） #

这种方式把两种匹配逻辑拆成两个独立的内连接查询，再合并结果，逻辑清晰，几乎所有数据库都支持：

-- 当key1非空时，内连接dimension1
SELECT fact.*, dim1.type AS type
FROM schema.fact_table AS fact
INNER JOIN schema.dimension1 AS dim1 
    ON fact.key1 = dim1.key1
WHERE fact.key1 IS NOT NULL

UNION ALL

-- 当key1为空时，内连接dimension2
SELECT fact.*, dim2.type AS type
FROM schema.fact_table AS fact
INNER JOIN schema.dimension2 AS dim2 
    ON fact.key2 = dim2.key2
WHERE fact.key1 IS NULL;

因为两种场景互斥（key1非空和key1为空不会同时发生），用UNION ALL比UNION效率更高，不需要去重。

方案2：用LATERAL JOIN/CROSS APPLY（适合支持的数据库） #

如果你的数据库支持LATERAL JOIN（比如PostgreSQL、Oracle 12c+）或者CROSS APPLY（SQL Server），可以用这种动态关联的写法，更简洁：

PostgreSQL/Oracle写法 #

SELECT fact.*, dim.type AS type
FROM schema.fact_table AS fact
INNER JOIN LATERAL (
    -- 优先匹配dimension1（当key1非空时）
    SELECT type FROM schema.dimension1 
    WHERE fact.key1 = dimension1.key1 AND fact.key1 IS NOT NULL
    UNION ALL
    -- 匹配dimension2（当key1为空时）
    SELECT type FROM schema.dimension2 
    WHERE fact.key2 = dimension2.key2 AND fact.key1 IS NULL
) AS dim ON true;

SQL Server写法 #

SELECT fact.*, dim.type AS type
FROM schema.fact_table AS fact
CROSS APPLY (
    SELECT type FROM schema.dimension1 
    WHERE fact.key1 = dimension1.key1 AND fact.key1 IS NOT NULL
    UNION ALL
    SELECT type FROM schema.dimension2 
    WHERE fact.key2 = dimension2.key2 AND fact.key1 IS NULL
) AS dim;

这种写法会针对每条fact记录，动态选择要关联的维度表，内连接保证只有匹配到维度记录的fact才会被返回。

方案3：用CASE表达式结合过滤（仅作参考） #

也可以先做左连接，再通过WHERE条件过滤掉未匹配到对应维度的记录，变相实现内连接效果，但逻辑相对绕，性能不如前两种方案：

SELECT fact.*,
       CASE WHEN fact.key1 IS NOT NULL THEN dim1.type ELSE dim2.type END AS type
FROM schema.fact_table AS fact
LEFT JOIN schema.dimension1 AS dim1 
    ON fact.key1 = dim1.key1
LEFT JOIN schema.dimension2 AS dim2 
    ON fact.key2 = dim2.key2
WHERE 
    -- 确保要么匹配到dim1，要么匹配到dim2
    (fact.key1 IS NOT NULL AND dim1.key1 IS NOT NULL)
    OR (fact.key1 IS NULL AND dim2.key2 IS NOT NULL);

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