MySQL 5.7.36多列Full Join失效问题求助

为什么得到空表 #

你的两张表中，Table_1的Variable_A=1, Variable_B=2，Table_2的Variable_A=5, Variable_B=6，没有任何一行满足Variable_A和Variable_B同时相等的条件。如果你的数据库不支持FULL OUTER JOIN（比如MySQL 8.0之前的版本），直接用FULL JOIN会返回空结果；即使支持FULL JOIN，SELECT *本身不会导致空表，但你需要确认数据库对FULL JOIN的支持情况。

正确的SQL写法 #

1. 支持FULL OUTER JOIN的数据库（PostgreSQL、SQL Server等） #

直接使用FULL OUTER JOIN，并明确列出所有列（避免SELECT *的潜在问题，比如列顺序或重复列）：

CREATE TABLE table_3 AS
SELECT
    COALESCE(t1.Variable_A, t2.Variable_A) AS Variable_A,
    COALESCE(t1.Variable_B, t2.Variable_B) AS Variable_B,
    t1.Variable_C,
    t1.Variable_D,
    t2.Variable_E,
    t2.Variable_F
FROM table_1 t1
FULL OUTER JOIN table_2 t2
    ON t1.Variable_A = t2.Variable_A
    AND t1.Variable_B = t2.Variable_B;

用COALESCE确保公共列不会出现NULL（如果其中一张表有值就取该值），最终Table_3会得到两行：

2. 不支持FULL OUTER JOIN的数据库（如MySQL） #

用LEFT JOIN + RIGHT JOIN + UNION ALL模拟FULL JOIN：

CREATE TABLE table_3 AS
-- 保留Table_1的所有行，匹配Table_2的行
SELECT
    t1.Variable_A,
    t1.Variable_B,
    t1.Variable_C,
    t1.Variable_D,
    t2.Variable_E,
    t2.Variable_F
FROM table_1 t1
LEFT JOIN table_2 t2
    ON t1.Variable_A = t2.Variable_A
    AND t1.Variable_B = t2.Variable_B

UNION ALL

-- 保留Table_2中未在Table_1匹配到的行
SELECT
    t2.Variable_A,
    t2.Variable_B,
    NULL AS Variable_C,
    NULL AS Variable_D,
    t2.Variable_E,
    t2.Variable_F
FROM table_2 t2
LEFT JOIN table_1 t1
    ON t1.Variable_A = t2.Variable_A
    AND t1.Variable_B = t2.Variable_B
WHERE t1.Variable_A IS NULL;

关于自动识别公共字段合并 #

SQL本身没有内置语法可以自动识别所有公共字段并进行JOIN，必须显式指定JOIN条件。如果需要自动化，可以通过查询数据库的系统表（比如PostgreSQL的information_schema.columns，MySQL的INFORMATION_SCHEMA.COLUMNS）获取两张表的公共列，再动态生成JOIN语句。例如，查询公共列的SQL：

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'table_1'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'table_2';

拿到公共列后，可以用脚本（比如Python、Shell）或存储过程动态拼接JOIN条件和SELECT列列表。

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