MySQL中WHERE语句破坏FULL JOIN结果的问题及解决问询

问题根源 #

1. NULL值判断错误：SQL中不能用=判断NULL，必须使用IS NULL，t1.date = null永远返回UNKNOWN，无法匹配任何行。
2. WHERE条件过滤过度：原查询的WHERE t1.date > '2022-01-01'会直接过滤掉t1.date为NULL的行（也就是t2独有的2022-02-01数据），因为NULL与任何值比较的结果都是UNKNOWN，会被WHERE子句排除。

修正方案 #

方案一：修正UNION查询的WHERE条件

针对左右连接的不同场景分别设置过滤条件：LEFT JOIN部分过滤t1的有效日期，RIGHT JOIN部分过滤t2的有效日期，确保t2独有的符合条件的行被保留：

SELECT *
FROM `t1`
LEFT JOIN `t2` ON `t2`.`date` = `t1`.`date`
WHERE `t1`.`date` > '2022-01-01'

UNION

SELECT *
FROM `t1`
RIGHT JOIN `t2` ON `t2`.`date` = `t1`.`date`
WHERE `t2`.`date` > '2022-01-01';

方案二：先生成日期集合再关联（逻辑更清晰）

先提取所有符合条件的日期（t1和t2中大于2022-01-01的日期），再分别左连接两张表，避免用UNION模拟FULL JOIN的复杂逻辑：

WITH all_dates AS (
    -- 获取所有需要保留的日期
    SELECT `date` FROM `t1` WHERE `date` > '2022-01-01'
    UNION
    SELECT `date` FROM `t2` WHERE `date` > '2022-01-01'
)
SELECT 
    t1.`date` AS `t1.date`,
    t1.`value` AS `t1.value`,
    t2.`date` AS `t2.date`,
    t2.`value` AS `t2.value`
FROM all_dates d
LEFT JOIN `t1` ON t1.`date` = d.`date`
LEFT JOIN `t2` ON t2.`date` = d.`date`;

两种方案都能得到预期结果：

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