关联三张表获取全量行（无匹配设为NULL）及查询性能优化

需求说明 #

需要关联house_Table、club_Table、storage_Table三张表，获取所有行数据，当某张表无匹配项时，对应字段值设为NULL。当前使用的SQL查询语句执行耗时较长，寻求优化方案。

示例输入 #

house_Table表数据： #

id  date         house_nbr  house_qty               
--------------------------------------
1   2025-01-18      101         50              
2   2025-01-19      102         60              

club_Table表数据： #

id  date       club_nbr   club_qty              
----------------------------------
1   2025-01-18  201         30              
3   2025-01-20  202         40              

storage_Table表数据： #

id  date          storage_nbr   storage_qty             
--------------------------------------------
1   2025-01-18      301             20              
4   2025-01-21      302             10              

示例输出 #

id  date    house_nbr   house_qty   club_nbr    club_qty    storage_nbr storage_qty
------------------------------------------------------------------------------------
1   2025-01-18  101 50  201 30  301 20
2   2025-01-19  102 60  NULL    NULL    NULL    NULL
3   2025-01-20  NULL    NULL    202 40  NULL    NULL
4   2025-01-21  NULL    NULL    NULL    NULL    302 10

当前使用的SQL语句 #

SELECT 
    COALESCE(s.id, d.id, f.id) AS id,
    COALESCE(s.date, d.date, f.date) AS date,
    s.house_nbr,
    s.house_qty,
    d.club_nbr,
    d.club_qty,
    f.storage_nbr,
    f.storage_qty
FROM 
    house s
FULL OUTER JOIN 
    club d ON s.id = d.id AND s.date = d.date
FULL OUTER JOIN 
    storage f ON COALESCE(s.id, d.id) = f.id AND COALESCE(s.date, d.date) = f.date
ORDER BY 
    id, date

优化方案 #

1. 重构查询逻辑，避免JOIN条件中使用函数 #

原查询在关联storage表时用了COALESCE函数，这会导致数据库无法利用索引，只能全表扫描。可以先通过UNION收集所有唯一的(id, date)组合，再分别左连接三张表：

WITH all_keys AS (
    SELECT id, date FROM house_Table
    UNION
    SELECT id, date FROM club_Table
    UNION
    SELECT id, date FROM storage_Table
)
SELECT 
    ak.id,
    ak.date,
    ht.house_nbr,
    ht.house_qty,
    ct.club_nbr,
    ct.club_qty,
    st.storage_nbr,
    st.storage_qty
FROM all_keys ak
LEFT JOIN house_Table ht ON ak.id = ht.id AND ak.date = ht.date
LEFT JOIN club_Table ct ON ak.id = ct.id AND ak.date = ct.date
LEFT JOIN storage_Table st ON ak.id = st.id AND ak.date = st.date
ORDER BY ak.id, ak.date;

UNION会自动去重生成所有需要关联的键，后续左连接逻辑清晰，且能利用索引快速匹配数据。

2. 添加复合索引 #

给三张表分别创建(id, date)的复合索引，让数据库在关联和查询时能直接通过索引定位数据，减少扫描范围：

CREATE INDEX idx_house_id_date ON house_Table(id, date);
CREATE INDEX idx_club_id_date ON club_Table(id, date);
CREATE INDEX idx_storage_id_date ON storage_Table(id, date);

如果表中数据量较大，索引能大幅降低查询耗时。

3. 检查字段数据类型一致性 #

确保三张表中id和date字段的数据类型完全一致（比如id都是INT，date都是DATE类型）。类型不一致会触发隐式转换，不仅无法使用索引，还会额外增加计算开销。

4. 优化排序逻辑 #

如果业务不需要强制排序，可以直接去掉ORDER BY子句；如果必须排序，可在CTE的UNION后显式排序（部分数据库中UNION会自动排序，但不依赖该特性的话建议显式指定），减少最终排序阶段的资源消耗。

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