any alternative to mysql left join copy table_B content in table_A without duplicates

Question

Given the two tables:

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);

I want to copy contents of each row from table_B to table_A except if the lines are duplicated. Correct output after applying a LEFT JOIN is:

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 |
+------+------+

The query I used is:

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
    )
);

This is simple because there are only two columns in the tables, but if I have to write the same query for tables with 20-30 columns it gets very complicated and big.

Are there alternatives to using the JOIN here or simplifying the ON and WHERE matches to include all columns?

Thanks in advance.

If the rows are duplicated, does it matter if they're copied? — Tangentially Perpendicular, Aug 29 '23 at 23:04
Table_A doesn't have duplicated rows on it's own. I'm trying to avoid exactly this. — Mircea Cristian, Aug 29 '23 at 23:17
What version of MySQL are you using? [EXCEPT clause](https://dev.mysql.com/doc/refman/8.0/en/except.html) was added in 8.0.31 — user1191247, Aug 29 '23 at 23:32
I was using mariaDB version 10.6.11 but I will take a look thanks. — Mircea Cristian, Aug 29 '23 at 23:58

score 0 · Answer 1 · answered Aug 30 '23 at 02:45

If you are using the version which doesn't support EXCEPT clause, I suppose using a correlated subquery instead of JOIN can make the coding more concise:

    insert table_a  
        select distinct * from table_b b 
        where not exists (select 1 from table_a a 
                        where b.col1=a.col1 and b.col2=a.col2)  ;

Note: Without the distinct keyword , duplicate inserts can happen if table b has duplicate rows.

score 0 · Accepted Answer · answered Aug 30 '23 at 07:37

As you are using MariaDB 10.6.11 you can use the EXCEPT clause, which was added in 10.3:

INSERT INTO table_A
SELECT * FROM table_B EXCEPT ALL SELECT * FROM table_A;

For your sample data, SELECT * FROM table_A; returns:

col1	col2
10	10
15	15
35	35
45	45
2000	2000
6000	6000

Here's a db<>fiddle.

any alternative to mysql left join copy table_B content in table_A without duplicates

2 Answers2