Want to compare 2 tables and save it to 3rd table

[LindyTaylor]

Hi

I have created three tables and now I want to compare two tables and save the matching results to existing third table. The problem is I cannot seem to save anything. Here is what I did

SELECT * FROM table1
WHERE tableA.columnB = tableB.columnB
ORDER BY columnB;

Then I tried JOIN, NATURAL JOIN, and INJOIN .. Maybe I had an indecent syntax..anyway nothing worked out so far.

FYI : Table A, B and C have the exact similar format. Column B is defined as an integer. My database exceed 5Gb..

[Kristine Habeck]

First, why you are trying to save the matching results in to a third table? As you said, all three tables have the similar format, then what makes sense to save the results in table A and tables B? It would be twice as many columns. Can you elaborate what exact columns you want to save?

[LindyTaylor]

Actually Table A and B filled with numbers and tables C is empty. FYI, each table has six columns of integers. To make each table I did this for each table

CREATE tableA(column1 int, column2 int, column3 int, column int, column5 int, column6);

What I want to see is, if between table A and B, the second column has the similar number, then I should copy the entire line (from column 1 to 6) into table C. So how can I transfer this to table third table C?

[Kristine Habeck]

You need to consider following solution >

INSERT
INTO tableC
SELECT tableA.*
FROM tableA
INNER
JOIN tableB
ON tableB.columnB = tableA.ColumnB

[LindyTaylor]

Thanks it works now. But now I have another question, what is the difference between INNER JOIN and NATURAL JOIN?

[Kristine Habeck]

The simple difference between both is how you specify which columns to join on.