SQL查询优化：替换重复UNION ALL与EXISTS关联子查询

问题背景 #

现有orders表存在日期缺失，需用前序日期数据填充，规则为单个arrival_date对应周一至周五数据，需覆盖前5天的日期偏移场景。原查询通过重复的UNION ALL和EXISTS子查询实现，存在代码冗余问题。

原查询 #

SELECT 
    arrival_date,
    order_id,
    order_date
FROM orders orders
UNION ALL
SELECT
    arrival_date + INTERVAL '1 day',
    order_id,
    order_date
FROM orders ord1
WHERE EXISTS (SELECT true FROM orders ord2 WHERE ord2.arrival_date = ord1.arrival_date + INTERVAL '1 day')
UNION ALL
SELECT
    arrival_date + INTERVAL '2 day',
    order_id,
    order_date
FROM orders ord1
WHERE EXISTS (SELECT true FROM orders ord2 WHERE ord2.arrival_date = ord1.arrival_date + INTERVAL '2 day')
UNION ALL
SELECT
    arrival_date + INTERVAL '3 day',
    order_id,
    order_date
FROM orders ord1
WHERE EXISTS (SELECT true FROM orders ord2 WHERE ord2.arrival_date = ord1.arrival_date + INTERVAL '3 day')
UNION ALL
SELECT
    arrival_date + INTERVAL '4 day',
    order_id,
    order_date
FROM orders ord1
WHERE EXISTS (SELECT true FROM orders ord2 WHERE ord2.arrival_date = ord1.arrival_date + INTERVAL '4 day')
UNION ALL
SELECT
    arrival_date + INTERVAL '5 day',
    order_id,
    order_date
FROM orders ord1
WHERE EXISTS (SELECT true FROM orders ord2 WHERE ord2.arrival_date = ord1.arrival_date + INTERVAL '5 day')

优化方案 #

1. 消除重复UNION ALL语句

使用数字序列生成+交叉连接替代重复的UNION ALL块，通过一次连接生成所有需要的日期偏移组合，大幅减少代码冗余。

2. 替换关联EXISTS子查询

提前提取所有已存在的arrival_date存入临时集合（CTE），用IN或直接关联替代EXISTS子查询，提升查询可读性与执行效率。

优化后完整SQL #

-- 提取所有已存在的arrival_date，避免重复查询
WITH existing_dates AS (
    SELECT DISTINCT arrival_date FROM orders
),
-- 生成0-5的日期偏移量，覆盖原日期及前5天
day_offsets AS (
    SELECT generate_series(0, 5) AS offset_days -- PostgreSQL语法
    -- 若为MySQL，替换为以下序列生成方式：
    -- SELECT 0 AS offset_days UNION ALL
    -- SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
SELECT
    o.arrival_date + INTERVAL '1 day' * do.offset_days AS arrival_date,
    o.order_id,
    o.order_date
FROM orders o
CROSS JOIN day_offsets do
-- 筛选出偏移后实际存在的日期
WHERE (o.arrival_date + INTERVAL '1 day' * do.offset_days) IN (SELECT arrival_date FROM existing_dates)
ORDER BY o.arrival_date, do.offset_days;

关键说明 #

• existing_dates CTE：去重提取所有有效的arrival_date，作为后续日期校验的数据源，避免多次扫描原表。
• day_offsets CTE：生成0到5的整数序列，其中0对应原日期，1-5对应前5天的偏移量；不同数据库可调整序列生成方式（如MySQL用UNION ALL，SQL Server用VALUES子句）。
• 交叉连接：一次性生成原表数据与所有偏移量的组合，再通过IN条件筛选出符合要求的记录，替代原有的多次UNION ALL和关联子查询。
• 若仅需保留偏移1-5天的记录，只需将day_offsets的序列调整为1-5即可。

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