无主键情况下合并两个已处理数据表的方法

嘿，这个场景我太熟悉了——刚接手数据库相关工作时，我也在多表连接上踩过坑，尤其是要兼顾不丢数据的情况。咱们一步步来搞定它！

核心需求是保留所有库存数据和所有订单数据，哪怕某个SKU只有库存没有订单，或者只有订单没有库存，这时候FULL OUTER JOIN就是最优选择——它会返回两个表中所有匹配和不匹配的行，完美避免数据丢失。

先假设你的表结构大概是这样（你可以根据实际字段调整）：

• 产品当前库存表：current_inventory，字段包括sku（产品唯一标识）、current_stock（当前库存数量）
• 全量订单表：all_orders，字段包括sku、order_date、quantity（订单数量）、order_type（标记历史/当前/未来订单）

方案1：聚合订单后再做全外连接（推荐） #

因为你的订单表有同一SKU的多笔订单，直接连接会导致库存行被重复显示（每笔订单对应一行库存），所以先对订单表按SKU聚合，统计不同类型的订单量，再和库存表连接，结果会更清晰。

完整SQL示例：

SELECT 
    -- 用COALESCE处理NULL：如果某SKU只有库存或只有订单，显示对应的值，否则取匹配的SKU
    COALESCE(i.sku, o.sku) AS sku,
    -- 没有库存的SKU，库存数量显示0
    COALESCE(i.current_stock, 0) AS current_stock,
    -- 没有对应订单的SKU，各类订单量显示0
    COALESCE(o.historical_orders, 0) AS historical_orders,
    COALESCE(o.current_orders, 0) AS current_orders,
    COALESCE(o.future_orders, 0) AS future_orders,
    COALESCE(o.total_orders, 0) AS total_orders
FROM current_inventory i
-- 全外连接聚合后的订单表
FULL OUTER JOIN (
    -- 子查询：按SKU分组，统计各类订单的总数量
    SELECT 
        sku,
        SUM(CASE WHEN order_type = '历史' THEN quantity ELSE 0 END) AS historical_orders,
        SUM(CASE WHEN order_type = '当前' THEN quantity ELSE 0 END) AS current_orders,
        SUM(CASE WHEN order_type = '未来' THEN quantity ELSE 0 END) AS future_orders,
        SUM(quantity) AS total_orders
    FROM all_orders
    GROUP BY sku
) o ON i.sku = o.sku
-- 按SKU排序，方便查看
ORDER BY sku;

这个方案的好处：

• 每一行对应一个SKU，不会有重复的库存数据
• 用COALESCE处理NULL值，结果更友好，不会出现空白
• 保留了所有SKU的库存和订单数据，完全避免丢失

方案2：如果你的数据库不支持FULL OUTER JOIN（比如MySQL） #

有些数据库（比如MySQL）原生不支持FULL OUTER JOIN，这时候可以用LEFT JOIN + RIGHT JOIN + UNION ALL来模拟全外连接的效果：

-- 第一步：左连接，保留所有库存数据，匹配对应的订单
SELECT 
    i.sku,
    i.current_stock,
    COALESCE(o.historical_orders, 0) AS historical_orders,
    COALESCE(o.current_orders, 0) AS current_orders,
    COALESCE(o.future_orders, 0) AS future_orders,
    COALESCE(o.total_orders, 0) AS total_orders
FROM current_inventory i
LEFT JOIN (
    SELECT 
        sku,
        SUM(CASE WHEN order_type = '历史' THEN quantity ELSE 0 END) AS historical_orders,
        SUM(CASE WHEN order_type = '当前' THEN quantity ELSE 0 END) AS current_orders,
        SUM(CASE WHEN order_type = '未来' THEN quantity ELSE 0 END) AS future_orders,
        SUM(quantity) AS total_orders
    FROM all_orders
    GROUP BY sku
) o ON i.sku = o.sku

-- 第二步：右连接，只保留没有对应库存的订单数据（避免和左连接重复）
UNION ALL

SELECT 
    o.sku,
    0 AS current_stock,
    o.historical_orders,
    o.current_orders,
    o.future_orders,
    o.total_orders
FROM current_inventory i
RIGHT JOIN (
    SELECT 
        sku,
        SUM(CASE WHEN order_type = '历史' THEN quantity ELSE 0 END) AS historical_orders,
        SUM(CASE WHEN order_type = '当前' THEN quantity ELSE 0 END) AS current_orders,
        SUM(CASE WHEN order_type = '未来' THEN quantity ELSE 0 END) AS future_orders,
        SUM(quantity) AS total_orders
    FROM all_orders
    GROUP BY sku
) o ON i.sku = o.sku
-- 过滤掉已经在左连接里的行（即库存表中存在的SKU）
WHERE i.sku IS NULL

ORDER BY sku;

小贴士：如果需要保留订单明细（不聚合） #

如果你不需要聚合订单，而是想看到每一笔订单和对应库存的关系，那可以直接做全外连接，但要注意库存行会被重复显示（每笔订单对应一行）：

SELECT 
    COALESCE(i.sku, o.sku) AS sku,
    COALESCE(i.current_stock, 0) AS current_stock,
    o.order_date,
    o.quantity,
    o.order_type
FROM current_inventory i
FULL OUTER JOIN all_orders o ON i.sku = o.sku
ORDER BY sku, order_date;

这个场景适合需要查看每笔订单对应的库存情况的需求，但结果行数会等于订单行数加上没有订单的库存行数。

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