MySQL - Create table from query results

In MySQL, you can create a table from a query results using the CREATE TABLE ... AS SELECT statement. That statement will create and populate a database table based on the results of the SELECT statement. Here is an example.

CREATE TABLE new_table
    SELECT * 
    FROM Contacts AS c
        LEFT JOIN Emails AS e ON
            c.id = e.Contact_id
    WHERE c.id < 10

Note

If you have duplicate column names due to multiple joins, MySQL will terminate your query with the following error message:

ERROR 1060 (42S21): Duplicate column name 'id'

In this case, you can't use SELECT * statement. You have to list all the column names and then ensure that each column name is unique by either rename it or discard it.