MySQL中如何按日期关联不同字段的两张表获正确结果？

现有两张表数据：

表raddb.Reg_Residual #

表raddb.Reg_Dioxido #

尝试执行以下SQL时得到笛卡尔积结果：

WITH
exp1 AS (SELECT AQuenteSan, AFriaSan, Realizacao FROM raddb.Reg_Residual WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15'),
exp2 AS (SELECT dioxcloro, Realizacao AS Realizacao1 FROM raddb.Reg_Dioxido WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15')
SELECT DISTINCT * FROM exp1 JOIN exp2;

期望得到按日期匹配的结果：

解决方法 #

问题核心是未指定JOIN的关联条件，导致数据库返回两张表的所有组合（笛卡尔积）。只需在JOIN后添加ON子句，通过日期字段匹配数据即可。

方案1：保留原SQL的字段重命名

如果需要保留Realizacao1字段，关联条件使用两张表的日期字段匹配：

WITH
exp1 AS (SELECT AQuenteSan, AFriaSan, Realizacao FROM raddb.Reg_Residual WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15'),
exp2 AS (SELECT dioxcloro, Realizacao AS Realizacao1 FROM raddb.Reg_Dioxido WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15')
SELECT * FROM exp1 JOIN exp2 ON exp1.Realizacao = exp2.Realizacao1;

方案2：简化SQL（无需重命名日期字段）

如果不需要重复的日期字段，可以直接用原字段关联，减少冗余：

WITH
exp1 AS (SELECT AQuenteSan, AFriaSan, Realizacao FROM raddb.Reg_Residual WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15'),
exp2 AS (SELECT dioxcloro, Realizacao FROM raddb.Reg_Dioxido WHERE Realizacao BETWEEN '2023-11-14' AND '2023-11-15')
SELECT exp1.AQuenteSan, exp1.AFriaSan, exp1.Realizacao, exp2.dioxcloro FROM exp1 JOIN exp2 ON exp1.Realizacao = exp2.Realizacao;

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