MySQL query to create a new table based on two tables

Question

primary_table:

name_column | description_column
--------------------------------
John        | asb
Marta       | oikjew89
Steve       | fr23

secondary_table:

name_column | lastname_column  | dob_column
-------------------------------------------
Marta       | Doe              | 1970-09-02
Steve       | Dobson           | 1968-04-01
Marta       | Simpson          | 1952-10-08
Michael     | Stone            | 1963-02-22

The the result of the query execution is a result_table table that contains:

name_column | description_column| lastname_column  | dob_column 
---------------------------------------------------------------
John        | asb               |   NULL           |   NULL
Marta       | oikjew89          | Doe              | 1970-09-02
Marta       | oikjew89          | Simpson          | 1952-10-08
Steve       | fr23              | Dobson           | 1968-04-01

which means the result_table is similar to primary_table but wider and has equal or more number of rows. It may get more rows as name_column value may occur more than 1 time in the secondary_table (ex. - Marta).

name_column rows in the secondary_table do not affect on anything if name_column value from the secondary_table is not met in the primary_table (ex. - Steve).

Based on the selection the result_table should be created.

As far as both tables have huge data (thousands of rows), name_column is indexed in both tables so the query execution won't take hours. First and last names are just an example. The actual data is absolutely different from people.

Thank you.

Answer 1

Here's how to create your result_table based on a select statement:

CREATE TABLE result_table AS
SELECT p.name_column, p.description_column, s.lastname_column, s.dob_column
FROM primary_table p
LEFT JOIN secondary_table s ON p.name_column = s.name_column;

Subsequently executing SELECT * FROM result_table ORDER BY name_column; will yield the following output:

+-------------+--------------------+-----------------+------------+
| name_column | description_column | lastname_column | dob_column |
+-------------+--------------------+-----------------+------------+
| John        | asb                | NULL            | NULL       |
| Marta       | oikjew89           | Doe             | 1970-09-02 |
| Marta       | oikjew89           | Simpson         | 1952-10-08 |
| Steve       | fr23               | Dobson          | 1968-04-01 |
+-------------+--------------------+-----------------+------------+