MySQL替代左连接：将table_B非重复行导入table_A的简化方案

表结构与初始数据 #

现有两张表的结构和初始数据如下：

create table table_A (col1 int, col2 int);

insert into table_A values(10,10);
insert into table_A values(15,15);
insert into table_A values(35,35);
insert into table_A values(45,45);

create table table_B (col1 int, col2 int);

insert into table_B values(10,10);
insert into table_B values(2000,2000);
insert into table_B values(35,35);
insert into table_B values(6000,6000);

需求说明 #

需要将table_B中的所有行复制到table_A，但要排除与table_A中已存在的重复行。执行完成后，预期输出如下：

select * from table_A;
+------+------+
| col1 | col2 |
+------+------+
|   10 |   10 |
|   15 |   15 |
|   35 |   35 |
|   45 |   45 |
| 2000 | 2000 |
| 6000 | 6000 |
+------+------+

select * from table_B;
+------+------+
| col1 | col2 |
+------+------+
|   10 |   10 |
| 2000 | 2000 |
|   35 |   35 |
| 6000 | 6000 |
+------+------+

当前解法的问题 #

目前使用的LEFT JOIN写法在列数较少时可行，但当表包含20-30列时，ON和WHERE子句需要逐列匹配，代码会变得冗长且不易维护：

INSERT IGNORE INTO test_leftjoin.table_A (
    SELECT DISTINCT test_leftjoin.table_B.*
    from test_leftjoin.table_B
    LEFT JOIN test_leftjoin.table_A
        ON (
            test_leftjoin.table_B.col1 = test_leftjoin.table_A.col1 and
            test_leftjoin.table_B.col2 = test_leftjoin.table_A.col2
        ) 
    WHERE (
        test_leftjoin.table_A.col1 IS NULL AND
        test_leftjoin.table_A.col2 IS NULL
    )
);

替代方案 #

方法1：使用NOT EXISTS子查询 #

无需JOIN，直接通过行记录匹配判断重复，语法更简洁，多列场景下只需按顺序列出列名即可：

INSERT INTO test_leftjoin.table_A
SELECT *
FROM test_leftjoin.table_B b
WHERE NOT EXISTS (
    SELECT 1
    FROM test_leftjoin.table_A a
    WHERE (a.col1, a.col2) = (b.col1, b.col2)
);

如果是多列，只需扩展括号内的列列表，比如(a.col1, a.col2, ..., a.col30) = (b.col1, b.col2, ..., b.col30)，避免了大量AND连接的条件。

方法2：利用唯一约束简化插入 #

先给table_A中需要判断重复的列添加联合唯一索引：

ALTER TABLE test_leftjoin.table_A
ADD UNIQUE INDEX idx_unique_cols (col1, col2);

之后直接使用INSERT IGNORE插入，数据库会自动跳过违反唯一约束的重复行：

INSERT IGNORE INTO test_leftjoin.table_A
SELECT * FROM test_leftjoin.table_B;

如果需要对重复行执行更新操作，也可以用ON DUPLICATE KEY UPDATE（此处仅做占位更新以跳过重复）：

INSERT INTO test_leftjoin.table_A
SELECT * FROM test_leftjoin.table_B
ON DUPLICATE KEY UPDATE col1 = col1;

这种方法是多列场景下最简洁的写法，前提是可以给目标表添加唯一约束。

方法3：使用EXCEPT集合运算（需数据库支持） #

如果使用的数据库支持EXCEPT（如MySQL 8.0.3+、PostgreSQL等），可以直接取table_B与table_A的差集再插入，无需指定任何列名：

INSERT INTO test_leftjoin.table_A
SELECT * FROM test_leftjoin.table_B
EXCEPT
SELECT * FROM test_leftjoin.table_A;

该方法要求两张表的结构完全一致，代码最为简洁。

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