SQL 合并两个时间段的销售数据：FULL OUTER JOIN + COALESCE

FULL OUTER JOIN 基础

FULL OUTER JOIN（全外连接）是SQL中一种连接操作，它会返回两个表中所有记录：

• 匹配的记录（两个表都有的数据）

• 左表独有的记录（右表对应部分为NULL）

• 右表独有的记录（左表对应部分为NULL）

MySQL中的实现方式

MySQL本身不支持FULL OUTER JOIN语法，但可以通过以下方式模拟：

left  join  + union (去除重复数据) + right  join 

​​场景​​: 合并两个时间段的销售数据

SELECT
    COALESCE(a.date, b.date) AS date,
    COALESCE(a.product_id, b.product_id) AS product_id,
    COALESCE(a.product_name, b.product_name) AS product_name,
    COALESCE(a.sales_quantity, 0) AS period1_sales,
    COALESCE(b.sales_quantity, 0) AS period2_sales,
    COALESCE(b.sales_quantity, 0) - COALESCE(a.sales_quantity, 0) AS sales_difference
FROM sales_period1 a
LEFT JOIN sales_period2 b 
    ON a.date = b.date AND a.product_id = b.product_id
UNION
SELECT
    COALESCE(a.date, b.date),
    COALESCE(a.product_id, b.product_id),
    COALESCE(a.product_name, b.product_name),
    COALESCE(a.sales_quantity, 0),
    COALESCE(b.sales_quantity, 0),
    COALESCE(b.sales_quantity, 0) - COALESCE(a.sales_quantity, 0)
FROM sales_period1 a
RIGHT JOIN sales_period2 b 
    ON a.date = b.date AND a.product_id = b.product_id
WHERE a.date IS NULL  -- 只取右表独有的数据
ORDER BY date, product_id;

​​思路​​:

• MySQL 无原生 FULL OUTER JOIN，用 LEFT JOIN + UNION + RIGHT JOIN 模拟。
• COALESCE 填充缺失值为 0 或默认值。

​​建表示例​​:

CREATE TABLE sales_period1 (
    date DATE,
    product_id INT,
    product_name VARCHAR(50),
    sales_quantity INT,
    PRIMARY KEY (date, product_id)
);

CREATE TABLE sales_period2 (
    date DATE,
    product_id INT,
    product_name VARCHAR(50),
    sales_quantity INT,
    PRIMARY KEY (date, product_id)
);

INSERT INTO sales_period1 (date, product_id, product_name, sales_quantity) VALUES
('2023-01-01', 1, 'Laptop', 10),
('2023-01-02', 2, 'Smartphone', 20);

INSERT INTO sales_period2 (date, product_id, product_name, sales_quantity) VALUES
('2023-01-01', 1, 'Laptop', 15),
('2023-01-03', 3, 'Headphones', 5);