how can I merge columns from 2 tables with different number of rows using SQLite?

Question

I have 2 tables, 'tab1' and 'tab2':

tab1 is:

col1
25.0
30.0
31.0
25.0

tab2 is:

col1    col2
25.0    11.0
30.0    99.0
31.0    57.0

I want to get the following merged table result by matching the col1 values in tab1 with col1 in tab2 (thus filling in using col2 values from tab2):

col1    col2
25.0    11.0
30.0    99.0
31.0    57.0
25.0    11.0

I am using this sqlite code currently:

INSERT INTO `merged_table1` 
SELECT * FROM tab1 LEFT JOIN tab2
ON tab1.col1 = tab2.col1;

However, the result is not correct (giving an extra column):

I believe I got it:INSERT INTO merged_table1` SELECT tab1.col1, tab2.col2 FROM tab1 LEFT JOIN tab2 ON tab1.col1= tab2.col1;` — user121
– user121, Commented Apr 19, 2017 at 4:20

CL. · Accepted Answer · 2017-04-19 07:22:11Z

1

If the columns actually have the same name, you can do the join using the USING clause, which automatically removes the duplicate column:

INSERT ...
SELECT *
FROM      tab1
LEFT JOIN tab2 USING (col1);

Otherwise, just tell the database which columns you want:

INSERT ...
SELECT tab1.col1,
       tab2.col2
FROM      tab1
LEFT JOIN tab2 ON tab1.col1 = tab2.col1;

answered Apr 19, 2017 at 7:22

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1