MySQL - How to create a new table that is a join on primary key of two existing tables

mysql join merge create-table
Richard H picture Richard H · Jan 21, 2010 · Viewed 60.5k times · Source

I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table.

How can this be done?

Answer

Max Shawabkeh picture Max Shawabkeh · Jan 21, 2010 
CREATE TABLE result AS 
  (SELECT first.*, 
          second.f1, 
          second.f2, 
          second.f3 
   FROM   first 
          INNER JOIN second 
                  ON first.id = second.id);

To get a view, do the same except replace "TABLE" with "VIEW". If you go with the table rather than the view, make sure to add a primary key as that will not be added by default.

Related questions

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More
How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Read More
INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse …

Read More