MySQL - Create table from query results

By xngo on December 8, 2019

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
   = e.Contact_id
    WHERE < 10


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.

About the author

Xuan Ngo is the founder of He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.