# CREATE TABLE AS

The `CREATE TABLE ... AS` statement creates a new table from a selection query.

## Intended use

Tables created with `CREATE TABLE ... AS` are intended to persist the result of a query for later reuse.

This can be more efficient than a view when the following two conditions are met:

- The result of the query is used as-is multiple times.
- The copy needs not be kept up-to-date with the original table over time.

When the results of a query are reused multiple times within a larger query, a view is advisable instead. The query optimizer can "peek" into the view and optimize the surrounding query using the primary key and indices of the tables mentioned in the view query.

A view is also advisable when the results must be up-to-date; a view always retrieves the current data from the tables that the view query mentions.

## Required privileges

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

## Synopsis

## 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.
`table_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` | A selection query to provide the data.

## Limitations

The primary key of tables created with `CREATE TABLE ... AS` is not derived from the query results. Like for other tables, it is not possible to add or change the primary key after creation. Moreover, these tables are not interleaved with other tables. The default rules for column families apply.

For example:

    > CREATE TABLE logoff (
        user_id INT PRIMARY KEY,
        user_email STRING UNIQUE,
        logoff_date DATE NOT NULL,
    );

    > CREATE TABLE logoff_copy AS TABLE logoff;

    > SHOW CREATE logoff_copy;

    +-------------+-----------------------------------------------------------------+
    |    Table    |                           CreateTable                           |
    +-------------+-----------------------------------------------------------------+
    | logoff_copy | CREATE TABLE logoff_copy (                                      |
    |             |     user_id INT NULL,                                           |
    |             |     user_email STRING NULL,                                     |
    |             |     logoff_date DATE NULL,                                      |
    |             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)  |
    |             | )                                                               |
    +-------------+-----------------------------------------------------------------+
    (1 row)

The example illustrates that the primary key, unique, and "not null" constraints are not propagated to the copy.

It is however possible to create a secondary index after `CREATE TABLE ... AS`.

For example:

    > CREATE INDEX logoff_copy_id_idx ON logoff_copy(user_id);

    > SHOW CREATE logoff_copy;

    +-------------+-----------------------------------------------------------------+
    |    Table    |                           CreateTable                           |
    +-------------+-----------------------------------------------------------------+
    | logoff_copy | CREATE TABLE logoff_copy (                                      |
    |             |     user_id INT NULL,                                           |
    |             |     user_email STRING NULL,                                     |
    |             |     logoff_date DATE NULL,                                      |
    |             |     INDEX logoff_copy_id_idx (user_id ASC),                     |
    |             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)  |
    |             | )                                                               |
    +-------------+-----------------------------------------------------------------+
    (1 row)

For maximum data storage optimization, consider using separately `CREATE` followed by `INSERT INTO ...` to populate the table using the query results.

## Examples

### Create a table from a `SELECT` query

    > 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

This statement creates a copy of an existing table but with changed 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     |
    +----+------------+

### Create a table from a `VALUES` clause

    > CREATE TABLE tech_states AS VALUES ('CA'), ('NY'), ('WA');

    > SELECT * FROM tech_states;

    +---------+
    | column1 |
    +---------+
    | CA      |
    | NY      |
    | WA      |
    +---------+
    (3 rows)

### Create a copy of an existing table

    > CREATE TABLE customers_ny_copy AS TABLE customers_ny;

    > SELECT * FROM customers_ny_copy;

    +----+------------+
    | id | first_name |
    +----+------------+
    |  6 | Dorotea    |
    | 15 | Thales     |
    +----+------------+

When a table copy is created this way, the copy is not associated to any primary key, secondary index or constraint that was present on the original table.

## See also

- Selection Queries
- Simple `SELECT` Clause
- `CREATE TABLE`
- `CREATE VIEW`
- `INSERT`
- `DROP TABLE`
- Other SQL Statements