mariadbleft-joinmariadb-10.6

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


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.


Solution

  • 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.