The CREATE TABLE AS statement creates a new table from the results of a SELECT statement.

These tables are not designed for long-term use, as they do not support some common table features like Primary Keys and interleaving. For similar functionality with more robust feature support, create a table and then use INSERT INTO SELECT.

Required Privileges

The user must have the CREATE privilege on the parent database.

Synopsis

CREATE TABLE IF NOT EXISTS any_name ( name , ) AS select_stmt

Parameters

Parameter Description
IF NOT EXISTS Create a new table only if a table of the same name does not already exist in the database; if one does exist, do not return an error.

Note that IF NOT EXISTS checks the table name only; it does not check if an existing table has the same columns, indexes, constraints, etc., of the new table.
any_name The name of the table to create, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as database.name.

The UPSERT and INSERT ON CONFLICT statements use a temporary table called excluded to handle uniqueness conflicts during execution. It’s therefore not recommended to use the name excluded for any of your tables.
name The name of the column you want to use instead of the name of the column from select_stmt.
select_stmt The SELECT statement whose results you want to use to create the table.

Examples

Create a Table from a SELECT Statement

> SELECT * FROM customers WHERE state = 'NY';
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+
> CREATE TABLE customers_ny AS SELECT * FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+

Change Column Names

> CREATE TABLE customers_ny (id, first_name) AS SELECT id, name FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;
+----+------------+
| id | first_name |
+----+------------+
|  6 | Dorotea    |
| 15 | Thales     |
+----+------------+

See Also



Yes No